Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 0F5BB475AFA
	for <pgsql-sql@postgresql.org>; Thu, 12 Sep 2002 10:02:07 -0400 (EDT)
Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178])
	by postgresql.org (Postfix) with ESMTP id 8458C475FA5
	for <pgsql-sql@postgresql.org>; Thu, 12 Sep 2002 10:02:02 -0400 (EDT)
Received: by megazone.bigpanda.com (Postfix, from userid 1001)
	id A0656D600; Thu, 12 Sep 2002 07:02:06 -0700 (PDT)
Received: from localhost (localhost [127.0.0.1])
	by megazone.bigpanda.com (Postfix) with ESMTP
	id 961995C02; Thu, 12 Sep 2002 07:02:06 -0700 (PDT)
Date: Thu, 12 Sep 2002 07:02:06 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Gaetano Mendola <mendola@bigfoot.com>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: Select the max on a field
In-Reply-To: <alq3mr$2s7o$1@news.hub.org>
Message-ID: <20020912065516.L45340-100000@megazone23.bigpanda.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200209/101
X-Sequence-Number: 9378


On Thu, 12 Sep 2002, Gaetano Mendola wrote:

> Hi all,
>
> Suppose that I have a table like this:
>
>
> att_1  |    att_2 |    att_3 |  att_4
> --------------------------------
> 1       |      a      |    y       |    y1
> 2       |      b      |    y       |    y2
> 3       |      a      |    xx     |    y3
> 4       |      c      |    zz      |    y4
> 5       |      a      |    t        |    y5
> 6       |      c      |    x       |    y6
>
>
>
> I want obtain all row that have for each att_2
> the max of att_1
>
> some like
>
> SELECT *
> FROM  MY_TABLE
> GROUP BY att_2
> HAVING att_1 = max ( id_user_log)

If you don't mind using postgres extensions and
don't need a particular att_1 ordering:

select distinct on (att_2) * from test
order by att_2, att_1 desc;

Otherwise I'm not sure you can do it without
a subselect somewhere.