From: | Matthew Wakeling <matthew(at)flymine(dot)org> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Sorted group by |
Date: | 2010-08-10 15:40:16 |
Message-ID: | alpine.DEB.2.00.1008101627440.2654@aragorn.flymine.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
I'm trying to eke a little bit more performance out of an application, and
I was wondering if there was a better way to do the following:
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. For instance, I have the following table
setup:
group | whatever type
value | whatever type
number | int
Index: group
I then have rows like this:
group | value | number
-------------------------------------
Foo | foo | 1
Foo | turnips | 2
Bar | albatross | 3
Bar | monkey | 4
I want to receive results like this:
group | value
-----------------------
Foo | turnips
Bar | monkey
Currently, I do this in my application by ordering by the number and only
using the last value. I imagine that this is something that can be done in
the new Postgres 9, with a sorted group by - something like this:
SELECT group, LAST(value, ORDER BY number) FROM table GROUP BY group
Is this something that is already built in, or would I have to write my
own LAST aggregate function?
Matthew
--
The third years are wandering about all worried at the moment because they
have to hand in their final projects. Please be sympathetic to them, say
things like "ha-ha-ha", but in a sympathetic tone of voice
-- Computer Science Lecturer
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Kellerer | 2010-08-10 15:56:46 | Re: Sorted group by |
Previous Message | Jeff Davis | 2010-08-09 22:41:59 | Re: Completely un-tuned Postgresql benchmark results: SSD vs desktop HDD |