Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 7622B475EB2
	for <pgsql-sql@postgresql.org>; Fri, 13 Sep 2002 11:33:58 -0400 (EDT)
Received: from beamish.nsd.ca (unknown [205.150.156.194])
	by postgresql.org (Postfix) with ESMTP id AD320475E6F
	for <pgsql-sql@postgresql.org>; Fri, 13 Sep 2002 11:33:57 -0400 (EDT)
Received: (from smap@localhost) by beamish.nsd.ca (8.9.3/8.9.3) id LAA18957;
	Fri, 13 Sep 2002 11:33:32 -0400
X-Authentication-Warning: beamish.nsd.ca: smap set sender to <jllachan@nsd.ca>
	using -f
Received: from reddog.nsd.ca(192.168.101.30) by beamish.nsd.ca via smap
	(V2.1/2.1+anti-relay+anti-spam)
	id xma018953; Fri, 13 Sep 02 11:33:05 -0400
Received: from nsd.ca (jllachan-linux.nsd.ca [192.168.101.148])
	by reddog.nsd.ca (8.8.7/8.8.7) with ESMTP id LAA28365;
	Fri, 13 Sep 2002 11:32:58 -0400
Message-ID: <3D8206A1.1B5B04FA@nsd.ca>
Date: Fri, 13 Sep 2002 11:39:13 -0400
From: Jean-Luc Lachance <jllachan@nsd.ca>
X-Mailer: Mozilla 4.77 [en] (X11; U; Linux 2.4.9-31 i686)
X-Accept-Language: en
MIME-Version: 1.0
To: Albrecht Berger <berger1517@gmx.ch>
Cc: pgsql <pgsql-sql@postgresql.org>
Subject: Re: sql group by statement
References: <002f01c25b10$6db4b940$a07ba8c0@wxp>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200209/137
X-Sequence-Number: 9414

How about:

select * from <Table> where (id, val2) in ( select id, max(val2) from
<Table> group by id);

JLL


Albrecht Berger wrote:
> 
> Hello,
> I have a problem, which I'm not able to solve with a simple query :
> 
> I need a resultset with distinct id's, but the max val2 of each id.
> I tried to group by id, but though I need the pk in my resultset
> I have to group it too, which "destroys" the group of val2.
> 
> Can this be done without a huge query ?
> 
> 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
> 
> 
> Thx
> berger
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org