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

"Last" is very non-portable, as you have found out.
You can do this fairly easily in PostgreSQL with
another non-portable syntax, SELECT DISTINCT ON ...

In this case, you want:

SELECT DISTINCT ON (group_key) id, group_key,
sort_key, data
FROM table
ORDER BY sort_key DESC;

You can also do this with subselects, but it's pretty
ugly...

--- Octavio Alvarez <alvarezp@octavio.ods.org> wrote:
>
> Hello! This is my first post to the list. I hope I'm
> not just blanked out,
> and asking a question with a simple answer. ;-)
>
> I need a query to return the last value of a set of
> grouped-by records
> when a column is sorted. For example:
>
> TABLE: (
>   id         integer UNIQUE,
>   group_key  integer,
>   sort_key   integer,
>   data       integer
> )
>
> Say it has the following rows:
>
>  id | group_key | sort_key | data
> ----+-----------+----------+------
>   1 |         1 |        1 |    1
>   2 |         1 |        2 |    6
>   3 |         1 |        5 |    6
>   4 |         1 |        9 |    2
>   5 |         2 |        3 |    1
>   6 |         2 |        4 |    3
>   7 |         2 |        7 |    3
>   8 |         3 |        6 |    5
>   9 |         3 |        3 |    4
> (9 rows)
>
> 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?
>
> Thank you very much.
>
> --
> Octavio Alvarez Piza.
> E-mail: alvarezp@octavio.ods.org
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster


__________________________________
Do you Yahoo!?
The New Yahoo! Search - Faster. Easier. Bingo.
http://search.yahoo.com