Subsorting GROUP BY data - Mailing list pgsql-sql

From Johnson, Michael L.
Subject Subsorting GROUP BY data
Date
Msg-id D9865BC8CAFD9547AF959660DE822764015FBC95@XMBIL133.northgrum.com
Whole thread Raw
Responses Re: Subsorting GROUP BY data
Re: Subsorting GROUP BY data
List 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


pgsql-sql by date:

Previous
From: "A. Kretschmer"
Date:
Subject: Re: Converting between UUID and VARCHAR
Next
From: "Oliveiros Cristina"
Date:
Subject: Re: Subsorting GROUP BY data