Re: Subsorting GROUP BY data - Mailing list pgsql-sql

From tv@fuzzy.cz
Subject Re: Subsorting GROUP BY data
Date
Msg-id 65199.89.102.139.23.1226330307.squirrel@sq.gransy.com
Whole thread Raw
In response to Subsorting GROUP BY data  ("Johnson, Michael L." <michael.l.johnson@ngc.com>)
List pgsql-sql
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?



pgsql-sql by date:

Previous
From: Tom Lane
Date:
Subject: Re: Using UTF strings in pg8.3 - storing hexadecimal values in bytea columns
Next
From: Mario Splivalo
Date:
Subject: Re: Using UTF strings in pg8.3 - storing hexadecimal values in bytea columns