On Tue, Aug 10, 2010 at 04:40:16PM +0100, Matthew Wakeling wrote: > > 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? this is trivially done when usign 'distinct on': select distinct on (group) * from table order by group desc, number desc; depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: depesz@xxxxxxxxxx / aim:depeszhdl / skype:depesz_hdl / gg:6749007 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
