Finding the "most recent" rows

From: Julian Scarfe <jas1(at)scigen(dot)co(dot)uk>
To: "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org>
Subject: Finding the "most recent" rows
Date: 1999-04-22 14:14:46
Message-ID: 371F2ED6.29FD9251@scigen.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Justin Long 1999-04-22 14:33:02 SELECT TOP _x_ ??
Previous Message Bill Carlson 1999-04-22 14:05:59 UNION with grouping?