Message-ID: <3FD7A80E.4000208@incentre.net>
Date: Wed, 10 Dec 2003 16:11:10 -0700
From: Guy Fraser <guy@incentre.net>
Organization: The Internet Centre
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.2.1) Gecko/20030225
MIME-Version: 1.0
To: Jodi Kanter <jkanter@virginia.edu>
Cc: Postgres SQL List <pgsql-sql@postgresql.org>
Subject: Re: picking max from list
References: <3FD783F6.6030801@virginia.edu>
In-Reply-To: <3FD783F6.6030801@virginia.edu>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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