Re: [SQL] Finding the "most recent" rows - Mailing list pgsql-sql

Julian Scarfe wrote: <blockquote type="CITE">I have a table (representing a set of observations) with datetime fields
anda <br />non-unique place field. <p>e.g. <br />create table obs ( <br />the_time datetime, <br />the_place char(8),
<br/>...other fields... <br />) <p>I'd like an efficient way to pull out the most recent row (i.e. highest <br
/>datatime)belonging to *each* of a number of places selected by a simple <br />query. <p>e.g. given a table such as:
<p>the_time   the_place   ... <br />0910        London <br />1130        London <br />0910        Paris <br
/>0930       London <br />0840        Paris <br />1020        London <br />0740        Paris <p>I'd like to select: <br
/>1130       London <br />0910        Paris <p>Most of my attempts at this (as an SQL novice) feel very clumsy and <br
/>inefficient.Is there an efficient way of doing this in SQL? <br />-- <p>Julian Scarfe</blockquote> If I understund
theproblem try this: <br /> <tt></tt><p><tt>test=> create table test (time datetime default now(), place
char(16));</tt><br/><tt>CREATE</tt><br /><tt>test=> insert into test (place) values ('London');</tt><br /><tt>INSERT
1948241</tt><br /><tt>test=> insert into test (place) values ('Paris');</tt><br /><tt>INSERT 194825 1</tt><br
/><tt>.............</tt><br/><tt>test=> insert into test (place) values ('Berlin');</tt><br /><tt>INSERT 194835
1</tt><br/><tt>test=> insert into test (place) values ('London');</tt><br /><tt>INSERT 194836 1</tt><br
/><tt>test=>insert into test (place) values ('Berlin');</tt><br /><tt>INSERT 194837 1</tt><br /><tt>test=> select
*from test;</tt><br /><tt>time                         |place</tt><br
/><tt>-----------------------------+----------------</tt><br/><tt>Thu 22 Apr 17:33:23 1999 EEST|London</tt><br
/><tt>Thu22 Apr 17:33:30 1999 EEST|Paris</tt><br /><tt>Thu 22 Apr 17:33:36 1999 EEST|London</tt><br /><tt>Thu 22 Apr
17:33:491999 EEST|Madrid</tt><br /><tt>Thu 22 Apr 17:33:54 1999 EEST|Paris</tt><br /><tt>Thu 22 Apr 17:34:03 1999
EEST|Berlin</tt><br/><tt>Thu 22 Apr 17:34:05 1999 EEST|Madrid</tt><br /><tt>Thu 22 Apr 17:34:08 1999
EEST|London</tt><br/><tt>Thu 22 Apr 17:34:12 1999 EEST|Paris</tt><br /><tt>Thu 22 Apr 17:34:14 1999 EEST|Madrid</tt><br
/><tt>Thu22 Apr 17:34:16 1999 EEST|Paris</tt><br /><tt>Thu 22 Apr 17:34:20 1999 EEST|Berlin</tt><br /><tt>Thu 22 Apr
17:34:221999 EEST|London</tt><br /><tt>Thu 22 Apr 17:34:31 1999 EEST|Berlin</tt><br /><tt>(14
rows)</tt><tt></tt><p><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><br /><tt>place           |time</tt><br
/><tt>----------------+-----------------------------</tt><br/><tt>Berlin          |Thu 22 Apr 17:34:31 1999
EEST</tt><br/><tt>London          |Thu 22 Apr 17:34:22 1999 EEST</tt><br /><tt>Madrid          |Thu 22 Apr 17:34:14
1999EEST</tt><br /><tt>Paris           |Thu 22 Apr 17:34:16 1999 EEST</tt><br /><tt>(4
rows)</tt><tt></tt><p><tt>test=></tt><p>Iuse PostgreSQL 6.5.0 beta1 on Red Hat LINUX 5.2 with 2.2.2 kernel.
<p>-- <br/> Best,<br />   George Moga,<br />   george@flex.ro<br />   Braila, ROMANIA <br />