Try
SELECT the_place, max(the_time) FROM the_place GROUP BY the_place;
Julian Scarfe wrote:
>
> I have a table (representing a set of observations) with datetime fields and a
> non-unique place field.
>
> e.g.
> create table obs (
> the_time datetime,
> the_place char(8),
> ...other fields...
> )
>
> I'd like an efficient way to pull out the most recent row (i.e. highest
> datatime) belonging to *each* of a number of places selected by a simple
> query.
>
> e.g. given a table such as:
>
> the_time the_place ...
> 0910 London
> 1130 London
> 0910 Paris
> 0930 London
> 0840 Paris
> 1020 London
> 0740 Paris
>
> I'd like to select:
> 1130 London
> 0910 Paris
>
> Most of my attempts at this (as an SQL novice) feel very clumsy and
> inefficient. Is there an efficient way of doing this in SQL?
> --
>
> Julian Scarfe
--
Chris Bitmead
http://www.bigfoot.com/~chris.bitmead
mailto:chris.bitmead@bigfoot.com