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
