Received: from ns1.prima.net.id ([202.57.0.8]) by hub.org (8.9.3/8.9.1) with ESMTP id WAA00615 for ; Thu, 22 Apr 1999 22:46:59 -0400 (EDT) (envelope-from chairudin@prima.net.id) Received: from prima.net.id (mail.prima.net.id [202.57.0.8]) by ns1.prima.net.id (8.8.4/8.7.2) with ESMTP id PAA10158; Fri, 23 Apr 1999 15:34:42 +0700 (GMT) Sender: root@ns1.prima.net.id Message-ID: <372030A2.5468EDF4@prima.net.id> Date: Fri, 23 Apr 1999 01:34:42 -0700 From: Chairudin Sentosa Reply-To: chairudin@prima.net.id Organization: PT. Khasanah Timur Indonesia X-Mailer: Mozilla 4.5 [en] (X11; I; SunOS 5.5.1 sun4u) X-Accept-Language: en MIME-Version: 1.0 To: Michael J Davis CC: "'Chris Bitmead'" , "pgsql-sql@postgresql.org" Subject: Re: [SQL] Finding the "most recent" rows References: <93C04F1F5173D211A27900105AA8FCFC145408@lambic.prevuenet.com> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit I tried this, and did not work. ibs=> select * from ibs_billing_record order by start_time desc limit 2; ERROR: parser: syntax error at or near "limit" Regards, Chai Michael J Davis wrote: > 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