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

test=> create table test (time datetime default now(), place char(16));
CREATE
test=> insert into test (place) values ('London');
INSERT 194824 1
test=> insert into test (place) values ('Paris');
INSERT 194825 1
.............
test=> insert into test (place) values ('Berlin');
INSERT 194835 1
test=> insert into test (place) values ('London');
INSERT 194836 1
test=> insert into test (place) values ('Berlin');
INSERT 194837 1
test=> select * from test;
time                         |place
-----------------------------+----------------
Thu 22 Apr 17:33:23 1999 EEST|London
Thu 22 Apr 17:33:30 1999 EEST|Paris
Thu 22 Apr 17:33:36 1999 EEST|London
Thu 22 Apr 17:33:49 1999 EEST|Madrid
Thu 22 Apr 17:33:54 1999 EEST|Paris
Thu 22 Apr 17:34:03 1999 EEST|Berlin
Thu 22 Apr 17:34:05 1999 EEST|Madrid
Thu 22 Apr 17:34:08 1999 EEST|London
Thu 22 Apr 17:34:12 1999 EEST|Paris
Thu 22 Apr 17:34:14 1999 EEST|Madrid
Thu 22 Apr 17:34:16 1999 EEST|Paris
Thu 22 Apr 17:34:20 1999 EEST|Berlin
Thu 22 Apr 17:34:22 1999 EEST|London
Thu 22 Apr 17:34:31 1999 EEST|Berlin
(14 rows)

test=> select place, time from test t where time = (select max(s.time) from test s where s.place = t.place) order by place;
place           |time
----------------+-----------------------------
Berlin          |Thu 22 Apr 17:34:31 1999 EEST
London          |Thu 22 Apr 17:34:22 1999 EEST
Madrid          |Thu 22 Apr 17:34:14 1999 EEST
Paris           |Thu 22 Apr 17:34:16 1999 EEST
(4 rows)

test=>

I use PostgreSQL 6.5.0 beta1 on Red Hat LINUX 5.2 with 2.2.2 kernel.

-- 
Best,
  George Moga,
  george@flex.ro
  Braila, ROMANIA