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: (view raw, whole thread or download thread mbox)
Lists: pgsql-sql
I have a table (representing a set of observations) with datetime fields and a
non-unique place field.

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

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


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-2017 The PostgreSQL Global Development Group