pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Re: SQL group select question 2+ messages / 2 participants [nested] [flat]
* Re: SQL group select question @ 2005-04-14 09:20 Ezequiel Tolnay <mail@etolnay.com.ar> 0 siblings, 0 replies; 2+ messages in thread From: Ezequiel Tolnay @ 2005-04-14 09:20 UTC (permalink / raw) To: Dave Roe <davidroe@email.com> > I have a table with a unique id, a name, a number and a value, for example: > > id name numb value > -- ---- ---- ----- > 1 tom 10 1000 > 2 dick 10 2000 > 3 harry 10 3000 > 4 dick 21 4000 > 5 harry 21 5000 > 6 harry 32 6000 > > As you can see, tom has 1 entry, dick has 2 and harry has 3. I would > like to select 3 records (one for each tom, dick and harry), based on > the associated number (id 1 should be chosen, because there is no other > 'tom' record, id 4 should be chosen over id 2, because 21 > 10, and id 6 > over ids 3 and 5, because 32 > 10 or 21). The value column is the data > that I am interested in: SELECT t.id, t.value FROM (SELECT name, MAX(numb) as numb FROM test1) AS s JOIN test1 t ON (t.name = s.name AND t.numb = s.numb); > id value > -- ----- > 6 6000 > 4 4000 > 1 1000 Cheers, Ezequiel Tolnay etolnay@gbtech.com.au ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: SQL group select question @ 2005-04-19 05:03 Ramakrishnan Muralidharan <ramakrishnanm@pervasive-postgres.com> 0 siblings, 0 replies; 2+ messages in thread From: Ramakrishnan Muralidharan @ 2005-04-19 05:03 UTC (permalink / raw) To: Dave Roe <davidroe@email.com>; pgsql-sql Hi, The following SQL query will fetch you result based on the example given below, SELECT T.ID , T.VALUE FROM ( SELECT MAX( NUMB ) AS NUMB , NAME FROM TEST3 GROUP BY NAME ) AS A , TEST3 T WHERE A.NAME = T.NAME AND A.NUMB = T.NUMB ORDER BY T.ID DESC Regards, R.Muralidharan ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-04-19 05:03 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2005-04-14 09:20 Re: SQL group select question Ezequiel Tolnay <mail@etolnay.com.ar> 2005-04-19 05:03 Re: SQL group select question Ramakrishnan Muralidharan <ramakrishnanm@pervasive-postgres.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox