Re: [GENERAL] 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general