Re: selecting random row values in postgres

got it....I just figured out that i dont need the ORDER BY clause even the first row selected by the 'DISTINCT ON' would solve the problem.

Thanks for all you help
-Sumeet.

On 2/23/07, Sumeet <asumeet@gmail.com> wrote:

Thanks Buddy, really appreciate ur help on this

....problem solved...

Is there any way this query can be optimized...i'm running it on a huge table with joins

- Sumeet

On 2/23/07, Rajesh Kumar Mallah <mallah.rajesh@gmail.com> wrote:

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

--
Thanks,
Sumeet Ambre
Master of Information Science Candidate,
Indiana University.

--
Thanks,
Sumeet Ambre
Master of Information Science Candidate,
Indiana University.