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

Rajarshi Guha wrote:

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

This should do it:

SELECT DISTINCT ON (pid) aid, pid, nmol
FROM foobar
WHERE pid IS NOT NULL
ORDER BY pid ASC, nmol DESC;

The pid ASC satisfies the requirement for the DISTINCT ON part, while
the nmol DESC ensures we get the MAX from each group. Or something like
that.

brian

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

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

On Jul 28, 2008, at 10:18 PM, Rajarshi Guha wrote:

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

Thanks to the posters for helpful solutions

- -------------------------------------------------------------------
Rajarshi Guha <rguha@indiana.edu>
GPG Fingerprint: D070 5427 CC5B 7938 929C DD13 66A1 922C 51E7 9E84
- -------------------------------------------------------------------
Alcohol, an alternative to your self
- 'Alcohol' by the Bare Naked Ladies

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

iEYEARECAAYFAkiOnG8ACgkQZqGSLFHnnoR2qQCeMntkTpqR/ZaVS/nY1izO5u5y
0FYAn0dwi8v0jSB4OvK4OnwMr+7ypQPp
=pNGY
-----END PGP SIGNATURE-----