Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-sql by date

Next:From: Justin LongDate: 1999-04-22 14:33:02
Subject: SELECT TOP _x_ ??
Previous:From: Bill CarlsonDate: 1999-04-22 14:05:59
Subject: UNION with grouping?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group