Re: a SQL query question

>
> 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
>
>  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
>

Normally this is a difficult sort of thing to do, but it's made easier
by a unique feature of Postgresql.  Please try the following:


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


More information can be found here:
http://www.postgresql.org/docs/8.3/interactive/sql-select.html#SQL-DISTINCT