Re: a SQL query question

Rajarshi Guha wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
> 
> Hi, I have a table of the form
> 
> aid  pid  nmol
> - ---  ---  ----
> 1    23   34
> 2    45   3445
> 3    23   100
> 4    78   12
> 5    45   14
> 6    45   200
> 7    null null
> 
> In general, aid is unique, pid and nmol are non-unique.
> 
> What I'm trying to do is to select those rows where pid is not null, 
> grouped by pid. So I'd get the following
> 
> aid  pid  nmol
> - ---  ---  ----
> 1    23   34
> 3    23   100
> 2    45   3445
> 5    45   14
> 6    45   200
> 4    78   12
 >
>  From within each group I'd like to select the row that has the maximum 
> value of nmol. So I'd end up with
> 
> aid  pid  nmol
> - ---  ---  ----
> 3    23   100
> 2    45   3445
> 4    78   12
> 
> I can easily do the first step, but am struggling to make the SQL for 
> the second step. Any pointers would be appreciated
> 

This should do it:

SELECT DISTINCT ON (pid) aid, pid, nmol
FROM foobar
WHERE pid IS NOT NULL
ORDER BY pid ASC, nmol DESC;

The pid ASC satisfies the requirement for the DISTINCT ON part, while 
the nmol DESC ensures we get the MAX from each group. Or something like 
that.

brian