From: | Craig James <craig_james(at)emolecules(dot)com> |
---|---|
To: | Dave Crooke <dcrooke(at)gmail(dot)com> |
Cc: | pgsql-performance <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Extracting superlatives - SQL design philosophy |
Date: | 2010-02-24 21:48:21 |
Message-ID: | 4B859EA5.2050706@emolecules.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Mose | 2010-02-24 21:50:24 | Re: Extracting superlatives - SQL design philosophy |
Previous Message | Garrett Murphy | 2010-02-24 21:43:20 | Re: Extracting superlatives - SQL design philosophy |