From: | Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
---|---|
To: | Matthew Wakeling <matthew(at)flymine(dot)org> |
Cc: | pgsql-performance <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Sorted group by |
Date: | 2010-08-11 15:54:17 |
Message-ID: | 1281541760-sup-2001@alvh.no-ip.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2010-08-11 22:31:26 | Re: Testing Sandforce SSD |
Previous Message | Tom Lane | 2010-08-11 15:23:21 | Re: 32 vs 64 bit build on Solaris Sparc |