Skip site navigation (1) Skip section navigation (2)

a SQL query question

From: Rajarshi Guha <rguha(at)indiana(dot)edu>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: a SQL query question
Date: 2008-07-29 02:18:06
Message-ID: F524AE22-BFF9-4908-9074-3497C35C6886@indiana.edu (view raw or flat)
Thread:
Lists: pgsql-general
-----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-----

Responses

pgsql-general by date

Next:From: Yi ZhaoDate: 2008-07-29 02:28:30
Subject: why can't I load pgxml.sql
Previous:From: Alvaro HerreraDate: 2008-07-29 01:21:18
Subject: Re: why my postgresql auto crashed???

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group