Re: MSAccess-like Last() with sorting before grouping

On Wed, May 14, 2003 at 17:50:04 -0700,
Octavio Alvarez <alvarezp(at)octavio(dot)ods(dot)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;