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