From: Ezequiel Tolnay <mail@etolnay.com.ar>
To: Dave Roe <davidroe@email.com>
Subject: Re: SQL group select question
Date: Thu, 14 Apr 2005 19:20:23 +1000
Message-ID: <425E35D7.7050300@etolnay.com.ar> (raw)
In-Reply-To: <yMqdna4cRbUZC8DfRVn-1w@comcast.com>
References: <yMqdna4cRbUZC8DfRVn-1w@comcast.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mail@etolnay.com.ar, davidroe@email.com
Subject: Re: SQL group select question
In-Reply-To: <425E35D7.7050300@etolnay.com.ar>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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