pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedpicking max from list
3+ messages / 3 participants
[nested] [flat]
* picking max from list
@ 2003-12-10 20:37 Jodi Kanter <jkanter@virginia.edu>
0 siblings, 2 replies; 3+ messages in thread
From: Jodi Kanter @ 2003-12-10 20:37 UTC (permalink / raw)
To: pgsql-sql
I have a query that produces results similar to this:
run# rd_pk group#
0 9209 5
1 9209 8
0 9520 2
1 9520 5
0 9520 etc....
0 8652
1 8652
2 8652
0 8895
1 8894
Ultimately I want to know the group number for EACH rd_pk with the
highest run number. Can this be done in one query? Or will I need to
code with a loop?
Thanks
Jodi
--
/_______________________________
//Jodi L Kanter
BioInformatics Database Administrator
University of Virginia
(434) 924-2846
jkanter@virginia.edu <mailto:jkanter@virginia.edu>/
/ /
/ /
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: picking max from list
@ 2003-12-10 20:53 Bruno Wolff III <bruno@wolff.to>
parent: Jodi Kanter <jkanter@virginia.edu>
1 sibling, 0 replies; 3+ messages in thread
From: Bruno Wolff III @ 2003-12-10 20:53 UTC (permalink / raw)
To: Jodi Kanter <jkanter@virginia.edu>; +Cc: pgsql-sql
On Wed, Dec 10, 2003 at 15:37:10 -0500,
Jodi Kanter <jkanter@virginia.edu> wrote:
> I have a query that produces results similar to this:
>
> run# rd_pk group#
> 0 9209 5
> 1 9209 8
> 0 9520 2
> 1 9520 5
> 0 9520 etc....
> 0 8652
> 1 8652
> 2 8652
> 0 8895
> 1 8894
>
> Ultimately I want to know the group number for EACH rd_pk with the
> highest run number. Can this be done in one query? Or will I need to
> code with a loop?
> Thanks
You can do this using the postgres distinct on extension.
The query would look something like:
select distinct on (rd_pk) run, rd_pk, group from table_name
order by run desc;
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: picking max from list
@ 2003-12-10 23:11 Guy Fraser <guy@incentre.net>
parent: Jodi Kanter <jkanter@virginia.edu>
1 sibling, 0 replies; 3+ messages in thread
From: Guy Fraser @ 2003-12-10 23:11 UTC (permalink / raw)
To: Jodi Kanter <jkanter@virginia.edu>; +Cc: pgsql-sql
select group,rd_pk
from (select ...) as your_query,
(select max(run) as max_run,rd_pk as rd
from (select ...) as your_query) as max_rd
where rd = rd_pk
and max_run = run;
I dont know if you call that one query but it should work.
There may be more elegant solutions, but I havn't had a chance to read
up on the new join types.
Good luck
Jodi Kanter wrote:
> I have a query that produces results similar to this:
>
> run# rd_pk group#
> 0 9209 5
> 1 9209 8
> 0 9520 2
> 1 9520 5
> 0 9520 etc....
> 0 8652
> 1 8652
> 2 8652
> 0 8895
> 1 8894
>
> Ultimately I want to know the group number for EACH rd_pk with the
> highest run number. Can this be done in one query? Or will I need to
> code with a loop?
> Thanks
> Jodi
--
Guy Fraser
Network Administrator
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-12-10 23:11 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-12-10 20:37 picking max from list Jodi Kanter <jkanter@virginia.edu>
2003-12-10 20:53 ` Bruno Wolff III <bruno@wolff.to>
2003-12-10 23:11 ` Guy Fraser <guy@incentre.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox