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

Try
select * from the_place order by the_time desc limit 2;

-----Original Message-----
From: Chris Bitmead [SMTP:chris(dot)bitmead(at)bigfoot(dot)com]
Sent: Thursday, April 22, 1999 8:45 AM
To: pgsql-sql(at)postgresql(dot)org
Subject: 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(dot)bitmead(at)bigfoot(dot)com