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 <[EMAIL PROTECTED]> wrote:
On 2/24/07, Sumeet <[EMAIL PROTECTED]> 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.testorder 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.testorder 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.
