Re: Subsorting GROUP BY data

If it is to Group the items by cat field then select the ID where the num is
the highest in group,

You could maybe try

SELECT a.ID, b.Cat,b.Num
FROM my_table a
JOIN ( SELECT cat, MAX(num) as maximo FROM my_table GROUP_BY cat) b
ON a.Cat = b.Cat
AND a.Num = b.maximo;

It 'll probably give what you need (with minor fixes...)

Best,
Oliveiros

----- Original Message -----
From: "Johnson, Michael L." <michael(dot)l(dot)johnson(at)ngc(dot)com>
To: <pgsql-sql(at)postgresql(dot)org>
Sent: Monday, November 10, 2008 2:56 PM
Subject: [SQL] Subsorting GROUP BY data

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