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