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
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? |