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