Re: a SQL query question

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