Content-Type: text/plain; charset=UTF-8
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Sorted group by
From: Alvaro Herrera <alvherre@commandprompt.com>
To: Matthew Wakeling <matthew@flymine.org>
In-reply-to: <alpine.DEB.2.00.1008101627440.2654@aragorn.flymine.org>
References: <alpine.DEB.2.00.1008101627440.2654@aragorn.flymine.org>
Date: Wed, 11 Aug 2010 11:54:17 -0400
Message-Id: <1281541760-sup-2001@alvh.no-ip.org>
User-Agent: Sup/git
Content-Transfer-Encoding: 8bit

Excerpts from Matthew Wakeling's message of mar ago 10 11:40:16 -0400 2010:

> I am trying to retrieve, for many sets of rows grouped on a couple of 
> fields, the value of an ungrouped field where the row has the highest 
> value in another ungrouped field.

I think this does what you want (schema is from the tenk1 table in the
regression database):

select string4 as group,
   (array_agg(stringu1 order by unique1 desc))[1] as value
from tenk1
group by 1 ;

Please let me know how it performs with your data.  The plan is rather simple:

regression=# explain analyze select string4 as group, (array_agg(stringu1 order by unique1 desc))[1] as value from tenk1 group by 1 ;
                                                          QUERY PLAN                                                           
───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
 GroupAggregate  (cost=0.00..1685.16 rows=4 width=132) (actual time=22.825..88.922 rows=4 loops=1)
   ->  Index Scan using ts4 on tenk1  (cost=0.00..1635.11 rows=10000 width=132) (actual time=0.135..33.188 rows=10000 loops=1)
 Total runtime: 89.348 ms
(3 filas)


-- 
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support