Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 297FB475D4B
	for <pgsql-sql@postgresql.org>; Fri, 13 Sep 2002 08:14:42 -0400 (EDT)
Received: from email04.aon.at (WARSL402PIP5.highway.telekom.at [195.3.96.79])
	by postgresql.org (Postfix) with SMTP id 065D1475D3F
	for <pgsql-sql@postgresql.org>; Fri, 13 Sep 2002 08:14:41 -0400 (EDT)
Received: (qmail 51440 invoked from network); 13 Sep 2002 12:14:43 -0000
Received: from m153p017.dipool.highway.telekom.at (HELO cantor) ([62.46.9.17])
	(envelope-sender <mkoi-pg@aon.at>)
	by qmail5rs.highway.telekom.at (qmail-ldap-1.03) with SMTP
	for <berger1517@gmx.ch>; 13 Sep 2002 12:14:43 -0000
From: Manfred Koizar <mkoi-pg@aon.at>
To: "Albrecht Berger" <berger1517@gmx.ch>
Cc: "pgsql" <pgsql-sql@postgresql.org>
Subject: Re: sql group by statement
Date: Fri, 13 Sep 2002 14:16:02 +0200
Message-ID: <g2l3ou44jver0jvic4477cd66d0k92c6li@4ax.com>
References: <002f01c25b10$6db4b940$a07ba8c0@wxp>
In-Reply-To: <002f01c25b10$6db4b940$a07ba8c0@wxp>
X-Mailer: Forte Agent 1.8/32.548
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200209/133
X-Sequence-Number: 9410

On Fri, 13 Sep 2002 12:29:21 +0200, "Albrecht Berger"
<berger1517@gmx.ch> wrote:
>Table :
>pk   id   val1 val2
> 1    1    2   3
> 2    1    2   4
> 3    2    1   1
> 4    1    0   5
> 5    2    1   8
> 
>
>Needed Result :
>pk   id   val1 val2
> 4    1    0   5
> 5    2    1   8

Albrecht,

"DISTINCT ON eliminates rows that match on all the specified
expressions, keeping only the first row of each set of duplicates."
So the trick is to sort appropriately:

	SELECT DISTINCT on (id) pk, id, val1, val2
	  FROM yourtable
	 ORDER BY id asc, val2 desc, pk desc;

Servus
 Manfred