Received: from flex.flex.ro (IDENT:root@[193.230.255.4]) by hub.org (8.9.3/8.9.1) with ESMTP id KAA69178 for ; Thu, 22 Apr 1999 10:49:41 -0400 (EDT) (envelope-from george@flex.ro) Received: from flex.ro (george.flex.ro [193.230.255.93]) by flex.flex.ro (8.9.1/8.9.1) with ESMTP id QAA01233; Thu, 22 Apr 1999 16:47:38 +0300 Sender: gestoc@flex.flex.ro Message-ID: <371F3646.C2A5C6E1@flex.ro> Date: Thu, 22 Apr 1999 17:46:31 +0300 From: George Moga X-Mailer: Mozilla 4.5 [en] (X11; I; Linux 2.2.2 i586) X-Accept-Language: ro MIME-Version: 1.0 To: Julian Scarfe , SQL PostgreSQL Subject: Re: [SQL] Finding the "most recent" rows References: <371F2ED6.29FD9251@scigen.co.uk> Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit 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

If I understund the problem try this:
 

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