Received: from corppo1.uvsg.com (corppo1.uvsg.com [192.40.65.90]) by hub.org (8.9.3/8.9.1) with ESMTP id KAA70939 for ; Thu, 22 Apr 1999 10:57:32 -0400 (EDT) (envelope-from michael.j.davis@tvguide.com) Received: from prevuepo1.prevuenet.com (prevuepo1.uvsg.com [192.40.65.44]) by corppo1.uvsg.com with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2448.0) id JCBQ6451; Thu, 22 Apr 1999 09:56:54 -0500 Received: by prevuepo1.uvsg.com with Internet Mail Service (5.5.2232.9) id ; Thu, 22 Apr 1999 09:59:54 -0500 Message-ID: <93C04F1F5173D211A27900105AA8FCFC145408@lambic.prevuenet.com> From: Michael J Davis To: "'Chris Bitmead'" , "pgsql-sql@postgresql.org" Subject: RE: [SQL] Finding the "most recent" rows Date: Thu, 22 Apr 1999 09:53:18 -0500 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2232.9) Content-Type: text/plain Try select * from the_place order by the_time desc limit 2; -----Original Message----- From: Chris Bitmead [SMTP:chris.bitmead@bigfoot.com] Sent: Thursday, April 22, 1999 8:45 AM To: pgsql-sql@postgresql.org Subject: Re: [SQL] Finding the "most recent" rows 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