X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 1A31C53603
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 14 Apr 2005 06:19:10 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 70608-10
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 14 Apr 2005 09:19:06 +0000 (GMT)
Received: from floppy.pyrenet.fr (floppy.pyrenet.fr [194.116.145.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 44CCA535FC
	for <pgsql-sql@postgresql.org>; Thu, 14 Apr 2005 06:19:05 -0300 (ADT)
Received: by floppy.pyrenet.fr (Postfix, from userid 106)
	id AC9FB30952; Thu, 14 Apr 2005 11:18:20 +0200 (MET DST)
From: Ezequiel Tolnay <mail@etolnay.com.ar>
X-Newsgroups: pgsql.sql
Subject: Re: SQL group select question
Date: Thu, 14 Apr 2005 19:20:23 +1000
Organization: Hub.Org Networking Services
Lines: 32
Message-ID: <425E35D7.7050300@etolnay.com.ar>
References: <yMqdna4cRbUZC8DfRVn-1w@comcast.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Complaints-To: usenet@news.hub.org
To: Dave Roe <davidroe@email.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
	rv:1.7.6) Gecko/20050319
X-Accept-Language: en-au, en-gb, en-us, en, es-ar, es-es
In-Reply-To: <yMqdna4cRbUZC8DfRVn-1w@comcast.com>
To: pgsql-sql@postgresql.org
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.021 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200504/265
X-Sequence-Number: 21158

> 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