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 D118752915 for ; Wed, 20 Jul 2005 11:32:37 -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 85294-08 for ; Wed, 20 Jul 2005 14:32:18 +0000 (GMT) Received: from floppy.pyrenet.fr (news.pyrenet.fr [194.116.145.2]) by svr1.postgresql.org (Postfix) with ESMTP id B8EF4528E4 for ; Wed, 20 Jul 2005 11:32:17 -0300 (ADT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id E647A3095A; Wed, 20 Jul 2005 16:38:32 +0200 (MET DST) From: Weinzierl Stefan X-Newsgroups: pgsql.sql Subject: Re: ids from grouped rows Date: Wed, 20 Jul 2005 16:09:23 +0200 Organization: T-Online Lines: 21 Message-ID: References: Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Complaints-To: usenet-abuse@t-online.de X-ID: ZZgDAUZF8eAsHvBJ7pUdig0KwoUxc3OErqDVo3+76jkvOH1cXDNGQX User-Agent: Mozilla Thunderbird 1.0.2 (X11/20050404) X-Accept-Language: de-DE, de, en-us, en In-Reply-To: To: pgsql-sql@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200507/213 X-Sequence-Number: 22191 Lindsay schrieb: > Lets say i do this: > > SELECT name, MAX(age) > FROM Person > GROUP BY name > > This will group people who have the same name together, and return the > highest age for each group. I want to be able to find the id for the > person with the highest age in each group - > > Ideally, it would be something like this > > SELECT name, MAX(age), id_for_row_with_max_age > FROM Person > GROUP BY name SELECT name, age, id FROM Person RIGHT JOIN (SELECT name, MAX(age) AS m FROM Person GROUP BY name) AS t ON (Person.name=t.name AND Person.age=t.m) Stefan