From: | "Fernando Hevia" <fhevia(at)ip-tel(dot)com(dot)ar> |
---|---|
To: | "'Johnson, Michael L(dot)'" <michael(dot)l(dot)johnson(at)ngc(dot)com>, <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: Subsorting GROUP BY data |
Date: | 2008-11-10 15:30:22 |
Message-ID: | 36D132D81D374E3D8284296729CA88BD@iptel.com.ar |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
> -----Mensaje original-----
> De: pgsql-sql-owner(at)postgresql(dot)org
> [mailto:pgsql-sql-owner(at)postgresql(dot)org] En nombre de Johnson,
> Michael L.
> Enviado el: Lunes, 10 de Noviembre de 2008 12:57
> Para: pgsql-sql(at)postgresql(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2008-11-10 15:48:00 | Re: Using UTF strings in pg8.3 - storing hexadecimal values in bytea columns |
Previous Message | Oliveiros Cristina | 2008-11-10 15:23:13 | Re: Subsorting GROUP BY data |