Re: [SQL] Finding the "most recent" rows

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