Thread: picking max from list

I have a query that produces results similar to this:<br /><br /> run#      rd_pk      group#<br /> 0           
9209     5   <br /> 1            9209      8<br /> 0            9520      2<br /> 1            9520      5<br /> 0     
     9520   etc....<br /> 0            8652<br /> 1            8652<br /> 2            8652<br /> 0            8895  
<br/> 1            8894<br /><br /> 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?<br /> Thanks<br /> Jodi<br /><div
class="moz-signature">--<br /></div><div class="Section1"><p class="MsoNormal"><i><span style="font-size: 9pt;
font-family:Arial;">_______________________________<br /></span></i><i><span style="font-size: 10pt;">Jodi L Kanter<br
/>BioInformatics Database Administrator<br /> University of Virginia<br /> (434) 924-2846<br /><a
href="mailto:jkanter@virginia.edu">jkanter@virginia.edu</a></span></i><spanstyle="font-size: 11pt; font-family:
Arial;"><brstyle="" /><br style="" /></span><p class="MsoNormal"><span style="font-size: 11pt; font-family:
Arial;"> </span><pclass="MsoNormal"><i><span style="font-size: 9pt; font-family: Arial;"> </span></i><p
class="MsoNormal"><i><spanstyle="font-size: 9pt; font-family: Arial;"> </span></i></div> 

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;

select group,rd_pk
from (select ...) as your_query,(select max(run) as max_run,rd_pk as rdfrom (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