From: | Sumeet <asumeet(at)gmail(dot)com> |
---|---|
To: | "Rajesh Kumar Mallah" <mallah(dot)rajesh(at)gmail(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: selecting random row values in postgres |
Date: | 2007-02-23 19:56:24 |
Message-ID: | 7539aebb0702231156m3db743f8j50b445fb846881da@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Rajesh Kumar Mallah | 2007-02-23 20:07:31 | Re: selecting random row values in postgres |
Previous Message | Rajesh Kumar Mallah | 2007-02-23 19:42:23 | Re: selecting random row values in postgres |