Dave Crooke wrote:
This is a generic SQL issue and not PG specific, but I'd like to get an opinion from this list.Consider the following data: # \d bar Table "public.bar" Column | Type | Modifiers --------+-----------------------------+----------- city | character varying(255) | temp | integer | date | timestamp without time zone | # select * from bar order by city, date; city | temp | date -----------+------+--------------------- Austin | 75 | 2010-02-21 15:00:00 Austin | 35 | 2010-02-23 15:00:00 Edinburgh | 42 | 2010-02-23 15:00:00 New York | 56 | 2010-02-23 15:00:00 New York | 78 | 2010-06-23 15:00:00 (5 rows) If you want the highest recorded temperature for a city, that's easy to do, since the selection criteria works on the same column that we are extracing: # select city, max(temp) from bar group by city order by 1; city | max -----------+----- Austin | 75 Edinburgh | 42 New York | 78 (3 rows) However there is (AFAIK) no simple way in plain SQL to write a query that performs such an aggregation where the aggregation criteria is on one column and you want to return another, e.g. adding the the *date of* that highest temperature to the output above, or doing a query to get the most recent temperature reading for each city.
If you add a unique-id column to your table that's filled in from a sequence, it becomes easy: select city, temp, date from bar where id in (select id from bar where ... whatever you like ...); Craig -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
