From: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
To: Sumeet <asumeet@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: selecting random row values in postgres
Date: Sat, 24 Feb 2007 01:12:23 +0530
Message-ID: <a97c77030702231142h155654a0id3f357b7f4834447@mail.gmail.com> (raw)
In-Reply-To: <7539aebb0702231129s48399f26r8881c6eff81b0708@mail.gmail.com>
References: <7539aebb0702231129s48399f26r8881c6eff81b0708@mail.gmail.com>
On 2/24/07, Sumeet <asumeet@gmail.com> wrote:
> Hi all,
>
> I'm trying to write a query to select random values from a set of 'GROUP
> BY'
> ....see the scenario below to understand the problem here (the actual
> problem cannot be discussed here so i'm taking an example scenario)
>
> Assume there is a table
>
> id | name | year_of_birth
>
> query: I want to select for each year_of_birth a random name.
Dear Sumeet
postgresql DISTINCT ON may be of help , but its not standard sql.
regds
mallah.
tradein_clients=> SELECT * from temp.test;
+----+------+-----+
| id | name | yob |
+----+------+-----+
| 1 | A | 2 |
| 2 | B | 2 |
| 3 | C | 2 |
| 4 | D | 1 |
| 5 | E | 1 |
| 6 | F | 1 |
+----+------+-----+
(6 rows)
tradein_clients=> SELECT distinct on (yob) id,name,yob from temp.test order
by yob,random();
+----+------+-----+
| id | name | yob |
+----+------+-----+
| 5 | E | 1 |
| 1 | A | 2 |
+----+------+-----+
(2 rows)
tradein_clients=> SELECT distinct on (yob) id,name,yob from temp.test order
by yob,random();
+----+------+-----+
| id | name | yob |
+----+------+-----+
| 4 | D | 1 |
| 1 | A | 2 |
+----+------+-----+
(2 rows)
>
> --> so i do a group by year_of_birth, now i have a set of names, is there
> any function to select just one name from these set of names.
> The current approach i'm using to solve this problem is
>
> 1) getting these names in a single string using a custom function
> 'group_concat'
> 2) Convert the single string into an array
> 3) use postgresql random function to generate a random number
> 4) us the random number to select a element from the array previously
> created.
>
> The solution is there but it's kinda hack, is there any other better way
of
> solving this problem.
>
>
> Thanks,
> Sumeet
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mallah.rajesh@gmail.com, asumeet@gmail.com
Subject: Re: selecting random row values in postgres
In-Reply-To: <a97c77030702231142h155654a0id3f357b7f4834447@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox