Sorted group by

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

Responses

Browse pgsql-performance by date

  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