Received: from tech.com.au (IDENT:root@techpt.lnk.telstra.net [139.130.75.122]) by hub.org (8.9.3/8.9.1) with ESMTP id KAA68253 for ; Thu, 22 Apr 1999 10:45:36 -0400 (EDT) (envelope-from chris.bitmead@bigfoot.com) Received: from bigfoot.com (chris@localhost [127.0.0.1]) by tech.com.au (8.8.7/8.8.7) with ESMTP id AAA04234 for ; Fri, 23 Apr 1999 00:45:13 +1000 Sender: chris@tech.com.au Message-ID: <371F35F9.607AF4B7@bigfoot.com> Date: Thu, 22 Apr 1999 14:45:13 +0000 From: Chris Bitmead X-Mailer: Mozilla 4.51 [en] (X11; I; Linux 2.0.36 i686) X-Accept-Language: en MIME-Version: 1.0 To: "pgsql-sql@postgresql.org" Subject: Re: [SQL] Finding the "most recent" rows References: <371F2ED6.29FD9251@scigen.co.uk> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Try SELECT the_place, max(the_time) FROM the_place GROUP BY the_place; Julian Scarfe wrote: > > 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 -- Chris Bitmead http://www.bigfoot.com/~chris.bitmead mailto:chris.bitmead@bigfoot.com