Rajarshi Guha wrote:
> What I'm trying to do is to select those rows where pid is not null,
> grouped by pid.
>
> From within each group I'd like to select the row that has the
> maximum value of nmol.
>
Distinct on should do the job for you.
select distinct on (pid) aid, pid, nmol
from atable
where pid is not null
order by pid, nmol desc
If you want the rows tie for max nmol within a pid then you can go to
select aid,pid,nmol
from atable
where (pid,nmol) in (select pid, max(nmol) from atable where pid is
not null group by pid)
klint.
--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350
Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: kgore4@une.edu.au