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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
