Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 88BF59FC362 for ; Fri, 23 Feb 2007 16:07:48 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 08796-07 for ; Fri, 23 Feb 2007 16:07:44 -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 0F3429FC37E for ; Fri, 23 Feb 2007 16:07:43 -0400 (AST) Received: by an-out-0708.google.com with SMTP id c24so454763ana for ; Fri, 23 Feb 2007 12:07:43 -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=nZGnMHMfUI2FGRiFWye0n0EDfqhiG9VLv+Eh+1F4tOn4sfQZexx0iNwIf2kJF8qn8bagvZivPQZN5t3lJnyyufO7Mgwj6ev7tklGadsMVksWcjlvTEdQR5/2M1le2d+YTHOTrfXSD5xY1GgxmIOViEwJm1VjtBh4de8h9JsoXuI= 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=BvOxOZK2XVVFvfRDaLNTFr6FZRuiuOGEv7gadd6iqB9wAtwejyiuMKsHhkqlGvBpGyahVgw2pCKBoxDbeZh8W+qB+cb5AszG86ARzoHKf/uP/tttgJoaDnDRn28VHJ2tGZ8Ej/3iIYZAY2QLsOVNeDWOLpblFeladeLUSl4Nclw= Received: by 10.115.89.1 with SMTP id r1mr1181147wal.1172261251884; Fri, 23 Feb 2007 12:07:31 -0800 (PST) Received: by 10.114.174.18 with HTTP; Fri, 23 Feb 2007 12:07:31 -0800 (PST) Message-ID: Date: Sat, 24 Feb 2007 01:37:31 +0530 From: "Rajesh Kumar Mallah" To: Sumeet Subject: Re: selecting random row values in postgres Cc: "Postgres SQL language list" In-Reply-To: <7539aebb0702231201u5068c139sad5434669dd129e5@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_49064_26236072.1172261251830" References: <7539aebb0702231129s48399f26r8881c6eff81b0708@mail.gmail.com> <7539aebb0702231156m3db743f8j50b445fb846881da@mail.gmail.com> <7539aebb0702231201u5068c139sad5434669dd129e5@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200702/244 X-Sequence-Number: 27578 ------=_Part_49064_26236072.1172261251830 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 2/24/07, Sumeet 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 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 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. > > > > > -- > Thanks, > Sumeet Ambre > Master of Information Science Candidate, > Indiana University. > ------=_Part_49064_26236072.1172261251830 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline

On 2/24/07, Sumeet <asumeet@gmail.com> 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 <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.

------=_Part_49064_26236072.1172261251830--