On 2/24/07, Sumeet <[EMAIL PROTECTED]> wrote:
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.
Dear Sumeet, if order by is not done there is no certainty about which row gets selected. usually same row keeps getting selected. but if you want a really random rows to come order by is required. if you need certainty about the particular row being selected order by a non random() column is required. regds mallah. Thanks for all you help
-Sumeet. On 2/23/07, Sumeet <[EMAIL PROTECTED]> 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 <[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. -- Thanks, Sumeet Ambre Master of Information Science Candidate, Indiana University.
