> -----Mensaje original----- > De: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] En nombre de Johnson, > Michael L. > Enviado el: Lunes, 10 de Noviembre de 2008 12:57 > Para: pgsql-sql@postgresql.org > Asunto: [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 wonder if this suites you: SELECT sub.cat, t.id, sub.Num FROM my_table t, ( SELECT cat, MAX(num) as Num FROM my_table GROUP_BY cat ) sub WHERE t.cat = sub.cat AND t.Num = sub.Num ORDER BY t.cat; Regards, Fernando. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql