Skip site navigation (1) Skip section navigation (2)

Re: Subsorting GROUP BY data

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 (view raw, whole thread or download thread mbox)
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.



In response to

Responses

pgsql-sql by date

Next:From: Tom LaneDate: 2008-11-10 15:48:00
Subject: Re: Using UTF strings in pg8.3 - storing hexadecimal values in bytea columns
Previous:From: Oliveiros CristinaDate: 2008-11-10 15:23:13
Subject: Re: Subsorting GROUP BY data

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group