a SQL query question

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

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

aid  pid  nmol
- ---  ---  ----
1    23   34
3    23   100
2    45   3445
5    45   14
6    45   200
4    78   12

 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



- -------------------------------------------------------------------
Rajarshi Guha  <rguha@indiana.edu>
GPG Fingerprint: D070 5427 CC5B 7938 929C  DD13 66A1 922C 51E7 9E84
- -------------------------------------------------------------------
"whois awk?", sed Grep.


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.8 (Darwin)

iEYEARECAAYFAkiOfd4ACgkQZqGSLFHnnoSOKACguioqdY0/Ut7su2KUYu+IRP7D
xOUAoKZsQKveWM52RTe422i3SRGWZk2u
=Xs+n
-----END PGP SIGNATURE-----