Re: MSAccess-like Last() with sorting before grouping - Mailing list pgsql-novice

On Wed, May 14, 2003 at 17:50:04 -0700,
  Octavio Alvarez <alvarezp@octavio.ods.org> wrote:
>
> I need it to have the following output:
>
>  id | group_key | sort_key | data
> ----+-----------+----------+------
>   4 |         1 |        9 |    2
>   7 |         2 |        7 |    3
>   8 |         3 |        6 |    5
>
> Which is, selecting the top value from sort_key from each different set of
> group_key. I can't program a last() function because it wouldn't work for
> group_key=3 in the example. ORDER BY always sorts the results after the
> grouping. I tried min/max functions but they apply to each column
> individually.
>
> Is there any way I can ask for this info to the SQL server?

If you are willing to use nonstandard sql, distinct on should do what you
want. Something like:

select distinct on (group_key) id, group_key, sort_key, data from table_name
order by sort_key desc;