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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
