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.l.johnson@ngc.com>
To: <pgsql-sql@postgresql.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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
