Subsorting GROUP BY data

From: "Johnson, Michael L(dot)" <michael(dot)l(dot)johnson(at)ngc(dot)com>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: Subsorting GROUP BY data
Date: 2008-11-10 14:56:30
Message-ID: D9865BC8CAFD9547AF959660DE822764015FBC95@XMBIL133.northgrum.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Given the following table:

ID | Cat | Num
----|-------|------
Z | A | 0
Y | A | 1
X | A | 2
W | B | 0
V | B | 1
U | B | 2
T | C | 0
S | C | 1
R | C | 2

I want to do this: Group the items by the cat field. Then select the
ID where the num is the highest in the group; so it should return
something like:

Cat | ID | Num
-----|------|------
A | X | 2
B | U | 2
C | R | 2

Using SQL like this, I can get the category and the highest # in the
category:

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?

Thanks in advance,
Mike Johnson

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message tv 2008-11-10 15:18:27 Re: Subsorting GROUP BY data
Previous Message A. Kretschmer 2008-11-10 14:44:42 Re: Converting between UUID and VARCHAR