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(at)indiana(dot)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-----