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.bitmead@bigfoot.com]Sent:    Thursday, April 22, 1999 8:45
AMTo:   pgsql-sql@postgresql.orgSubject:    Re: [SQL] Finding the "most recent" rows
 

TrySELECT 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 Bitmeadhttp://www.bigfoot.com/~chris.bitmeadmailto:chris.bitmead@bigfoot.com