Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 9D9309FB769 for ; Fri, 23 Feb 2007 15:56:32 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 73338-05 for ; Fri, 23 Feb 2007 15:56:28 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from an-out-0708.google.com (an-out-0708.google.com [209.85.132.240]) by postgresql.org (Postfix) with ESMTP id 578C89FC3B1 for ; Fri, 23 Feb 2007 15:56:28 -0400 (AST) Received: by an-out-0708.google.com with SMTP id c24so452256ana for ; Fri, 23 Feb 2007 11:56:27 -0800 (PST) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=j1J4/WBQNatGFrZauKM5g8TRs7bhGQVdFVfKJgVEdh5unWoJVsSNbwW/wMIbKsiSkT9/K9lAfVsH9Mg/DmPmGyDk1yHemdvDElZ5MOvzYpd+oqLr2xL+r8HALHPBnoOjy7O9MxKoNZD1SBgmeq99EPfFXYDl6x1NqrW9Sf0EFhs= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=ouAEWzkdXMKjmtw4uL+vXH0DUtuGuyzWfl+M0jESAEtv2n6M5msAx8lUgKjgMe1hmU661GPEQLL5bDw3cwzVUikFuk1lBydMeWz0RSO8CHhf54ueOtDqRvF5+X8ebyzibbSINQozBm8P3LodNdk+T3fox2YbQd7V+7iU2PCbiEU= Received: by 10.100.10.20 with SMTP id 20mr1836853anj.1172260584282; Fri, 23 Feb 2007 11:56:24 -0800 (PST) Received: by 10.100.11.6 with HTTP; Fri, 23 Feb 2007 11:56:24 -0800 (PST) Message-ID: <7539aebb0702231156m3db743f8j50b445fb846881da@mail.gmail.com> Date: Fri, 23 Feb 2007 14:56:24 -0500 From: Sumeet To: "Rajesh Kumar Mallah" Subject: Re: selecting random row values in postgres Cc: pgsql-sql@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_5390_8493447.1172260584096" References: <7539aebb0702231129s48399f26r8881c6eff81b0708@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200702/243 X-Sequence-Number: 27577 ------=_Part_5390_8493447.1172260584096 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 wrote: > > > > On 2/24/07, Sumeet 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. ------=_Part_5390_8493447.1172260584096 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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. ------=_Part_5390_8493447.1172260584096--