From: | "Rajesh Kumar Mallah" <mallah(dot)rajesh(at)gmail(dot)com> |
---|---|
To: | Sumeet <asumeet(at)gmail(dot)com> |
Cc: | "Postgres SQL language list" <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: selecting random row values in postgres |
Date: | 2007-02-23 20:07:31 |
Message-ID: | a97c77030702231207v7287c524h6e8c3f4a10f44371@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On 2/24/07, Sumeet <asumeet(at)gmail(dot)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(at)gmail(dot)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(dot)rajesh(at)gmail(dot)com> wrote:
> > >
> > >
> > >
> > > On 2/24/07, Sumeet <asumeet(at)gmail(dot)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.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.
>
From | Date | Subject | |
---|---|---|---|
Next Message | Tommy Gildseth | 2007-02-23 20:45:27 | Re: selecting random row values in postgres |
Previous Message | Sumeet | 2007-02-23 19:56:24 | Re: selecting random row values in postgres |