Re: Select the max on a field - Mailing list pgsql-sql

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.