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