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