Message-ID: <4B859EA5.2050706@emolecules.com>
Date: Wed, 24 Feb 2010 13:48:21 -0800
From: Craig James <craig_james@emolecules.com>
User-Agent: Thunderbird 2.0.0.23 (X11/20090817)
MIME-Version: 1.0
To: Dave Crooke <dcrooke@gmail.com>
CC: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Extracting superlatives - SQL design philosophy
References: <ca24673e1002241331l4099482aqc73649d93611767a@mail.gmail.com>
In-Reply-To: <ca24673e1002241331l4099482aqc73649d93611767a@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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