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(at)octavio(dot)ods(dot)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(at)octavio(dot)ods(dot)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