Quick Links

Re: [SQL] Finding the "most recent" rows

From:	George Moga <george(at)flex(dot)ro>
To:	Julian Scarfe <jas1(at)scigen(dot)co(dot)uk>, SQL PostgreSQL <pgsql-sql(at)postgreSQL(dot)org>
Subject:	Re: [SQL] Finding the "most recent" rows
Date:	1999-04-22 14:46:31
Message-ID:	371F3646.C2A5C6E1@flex.ro
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Thread:
Lists:	pgsql-sql

<!doctype html public "-//w3c//dtd html 4.0 transitional//en">
<html>
Julian Scarfe wrote:
<blockquote TYPE=CITE>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</blockquote>
If I understund the problem try this:
  <tt></tt>
<tt>test=> create table test (time datetime default now(), place char(16));</tt>
 <tt>CREATE</tt>
 <tt>test=> insert into test (place) values ('London');</tt>
 <tt>INSERT 194824 1</tt>
 <tt>test=> insert into test (place) values ('Paris');</tt>
 <tt>INSERT 194825 1</tt>
 <tt>.............</tt>
 <tt>test=> insert into test (place) values ('Berlin');</tt>
 <tt>INSERT 194835 1</tt>
 <tt>test=> insert into test (place) values ('London');</tt>
 <tt>INSERT 194836 1</tt>
 <tt>test=> insert into test (place) values ('Berlin');</tt>
 <tt>INSERT 194837 1</tt>
 <tt>test=> select * from test;</tt>
 <tt>time                        
|place</tt>
 <tt>-----------------------------+----------------</tt>
 <tt>Thu 22 Apr 17:33:23 1999 EEST|London</tt>
 <tt>Thu 22 Apr 17:33:30 1999 EEST|Paris</tt>
 <tt>Thu 22 Apr 17:33:36 1999 EEST|London</tt>
 <tt>Thu 22 Apr 17:33:49 1999 EEST|Madrid</tt>
 <tt>Thu 22 Apr 17:33:54 1999 EEST|Paris</tt>
 <tt>Thu 22 Apr 17:34:03 1999 EEST|Berlin</tt>
 <tt>Thu 22 Apr 17:34:05 1999 EEST|Madrid</tt>
 <tt>Thu 22 Apr 17:34:08 1999 EEST|London</tt>
 <tt>Thu 22 Apr 17:34:12 1999 EEST|Paris</tt>
 <tt>Thu 22 Apr 17:34:14 1999 EEST|Madrid</tt>
 <tt>Thu 22 Apr 17:34:16 1999 EEST|Paris</tt>
 <tt>Thu 22 Apr 17:34:20 1999 EEST|Berlin</tt>
 <tt>Thu 22 Apr 17:34:22 1999 EEST|London</tt>
 <tt>Thu 22 Apr 17:34:31 1999 EEST|Berlin</tt>
 <tt>(14 rows)</tt><tt></tt>
<tt>test=> select place, time from test t where time = (select max(s.time)
from test s where s.place = t.place) order by place;</tt>
 <tt>place          
|time</tt>
 <tt>----------------+-----------------------------</tt>
 <tt>Berlin          |Thu
22 Apr 17:34:31 1999 EEST</tt>
 <tt>London          |Thu
22 Apr 17:34:22 1999 EEST</tt>
 <tt>Madrid          |Thu
22 Apr 17:34:14 1999 EEST</tt>
 <tt>Paris          
|Thu 22 Apr 17:34:16 1999 EEST</tt>
 <tt>(4 rows)</tt><tt></tt>
<tt>test=></tt>
I use PostgreSQL 6.5.0 beta1 on Red Hat LINUX 5.2 with 2.2.2 kernel.
--  
Best, 
  George Moga, 
  george(at)flex(dot)ro 
  Braila, ROMANIA
  </html>

Attachment	Content-Type	Size
unknown_filename	text/html	3.8 KB

In response to

Finding the "most recent" rows at 1999-04-22 14:14:46 from Julian Scarfe

Browse pgsql-sql by date

	From	Date	Subject
Next Message	Michael J Davis	1999-04-22 14:53:18	RE: [SQL] Finding the "most recent" rows
Previous Message	Chris Bitmead	1999-04-22 14:45:13	Re: [SQL] Finding the "most recent" rows