Skip site navigation (1) Skip section navigation (2)

Re: Subsorting GROUP BY data

From: tv(at)fuzzy(dot)cz
To: "Johnson, Michael L(dot)" <michael(dot)l(dot)johnson(at)ngc(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Subsorting GROUP BY data
Date: 2008-11-10 15:18:27
Message-ID: 65199.89.102.139.23.1226330307.squirrel@sq.gransy.com (view raw or flat)
Thread:
Lists: 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?


In response to

pgsql-sql by date

Next:From: Oliveiros CristinaDate: 2008-11-10 15:23:13
Subject: Re: Subsorting GROUP BY data
Previous:From: Johnson, Michael L.Date: 2008-11-10 14:56:30
Subject: Subsorting GROUP BY data

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group