Re: Subsorting GROUP BY data

What about replacing the table by

SELECT * FROM my_table ORDER BY num

i.e. something like

SELECT cat, LAST(id), LAST(num) FROM (SELECT * FROM my_table ORDER BY num)
AS foo GROUP_BY cat;

Hope it works, just guessing it might help :-)

regards
Tomas

> SELECT cat, MAX(num) FROM my_table GROUP_BY cat;
>
> But if I add the "id" column, of course it doesn't work, since it's not
> in an aggregate function or in the GROUP_BY clause.  So I found a post
> at http://archives.postgresql.org/pgsql-hackers/2006-03/msg01324.php
> which describes how to add a "FIRST" and "LAST" aggregate function to
> PGSQL.  However, first and last don't seem to help unless you are able
> to "subsort" the grouping by the # (ie, group by cat, then subsort on
> num, and select the "last" one of the group).
>
> I would think something like the following would work, except that PGSQL
> does not like the SQL generated (it basically says I can't have a
> GROUP_BY after an ORDER_BY).  And if I move the "ORDER_BY" to the end,
> that just orders the returned groupings, so that doesn't help me either.
>
> SELECT cat, LAST(id), LAST(num) FROM my_table ORDER_BY num GROUP_BY cat;
>
>
> So does anyone know how to sort *within* a grouping so that FIRST and
> LAST return meaningful results?