Re: selecting random row values in postgres - Mailing list pgsql-sql

<br /><br />On 2/24/07, Sumeet <<a href="mailto:asumeet@gmail.com">asumeet@gmail.com</a>> wrote:<br />> Hi
all,<br/>> <br />> I'm trying to write  a query to select random values from a set of 'GROUP<br />> BY' <br
/>>....see the scenario below to understand the problem here (the actual<br />> problem cannot be discussed here
soi'm taking an example scenario) <br />> <br />> Assume there is a table<br />> <br />> id | name |
year_of_birth<br />> <br />> query: I want  to select for each year_of_birth a random name.<br /><br />Dear
Sumeet<br/><br />postgresql DISTINCT ON may be of help , but its not standard sql.<br /><br />regds<br />mallah.<br
/><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier
new,monospace;">tradein_clients=>SELECT * from temp.test;</span><br style="font-family: courier new,monospace;"
/><spanstyle="font-family: courier new,monospace;">+----+------+-----+</span><br style="font-family: courier
new,monospace;"/><span style="font-family: courier new,monospace;">| id | name | yob |</span><br style="font-family:
couriernew,monospace;" /><span style="font-family: courier new,monospace;"> +----+------+-----+</span><br
style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;">|  1 | A    |   2
|</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;"> |  2 |
B    |  2 |</span><br style="font-family: courier new,monospace;" /><span style="font-family: courier
new,monospace;">|  3| C    |   2 |</span><br style="font-family: courier new,monospace;" /><span style="font-family:
couriernew,monospace;"> |  4 | D    |   1 |</span><br style="font-family: courier new,monospace;" /><span
style="font-family:courier new,monospace;">|  5 | E    |   1 |</span><br style="font-family: courier new,monospace;"
/><spanstyle="font-family: courier new,monospace;"> |  6 | F    |   1 |</span><br style="font-family: courier
new,monospace;"/><span style="font-family: courier new,monospace;">+----+------+-----+</span><br style="font-family:
couriernew,monospace;" /><span style="font-family: courier new,monospace;"> (6 rows)</span><br style="font-family:
couriernew,monospace;" /><br style="font-family: courier new,monospace;" /><span style="font-family: courier
new,monospace;">tradein_clients=>SELECT distinct on (yob) id,name,yob  from temp.test order by
yob,random();</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier
new,monospace;">+----+------+-----+</span><brstyle="font-family: courier new,monospace;" /><span style="font-family:
couriernew,monospace;"> | id | name | yob |</span><br style="font-family: courier new,monospace;" /><span
style="font-family:courier new,monospace;">+----+------+-----+</span><br style="font-family: courier new,monospace;"
/><spanstyle="font-family: courier new,monospace;"> |  5 | E    |   1 |</span><br style="font-family: courier
new,monospace;"/><span style="font-family: courier new,monospace;">|  1 | A    |   2 |</span><br style="font-family:
couriernew,monospace;" /><span style="font-family: courier new,monospace;"> +----+------+-----+</span><br
style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;">(2 rows)</span><br
style="font-family:courier new,monospace;" /><br style="font-family: courier new,monospace;" /><span
style="font-family:courier new,monospace;">tradein_clients=> SELECT distinct on (yob) id,name,yob  from temp.test
orderby yob,random();</span><br style="font-family: courier new,monospace;" /><span style="font-family: courier
new,monospace;">+----+------+-----+</span><br style="font-family: courier new,monospace;" /><span style="font-family:
couriernew,monospace;">| id | name | yob |</span><br style="font-family: courier new,monospace;" /><span
style="font-family:courier new,monospace;"> +----+------+-----+</span><br style="font-family: courier new,monospace;"
/><spanstyle="font-family: courier new,monospace;">|  4 | D    |   1 |</span><br style="font-family: courier
new,monospace;"/><span style="font-family: courier new,monospace;"> |  1 | A    |   2 |</span><br style="font-family:
couriernew,monospace;" /><span style="font-family: courier new,monospace;">+----+------+-----+</span><br
style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;"> (2 rows)</span><br
style="font-family:courier new,monospace;" /><br style="font-family: courier new,monospace;" /><br style="font-family:
couriernew,monospace;" /><br style="font-family: courier new,monospace;" /><br />> <br />> --> so i do a group
byyear_of_birth, now i have a set of names, is there <br />> any function to select just one name from these set of
names.<br />> The current approach i'm using to solve this problem is <br />> <br />> 1) getting these names
ina single string using a custom function <br />> 'group_concat'<br />> 2) Convert the single string into an
array<br />> 3) use postgresql random function to generate  a random number <br />> 4) us the random number to
selecta element from the array previously <br />> created.<br />> <br />> The solution is there but it's kinda
hack,is there any other better way of<br />> solving this problem.<br />> <br />> <br />> Thanks, <br
/>>Sumeet <br />