Received: from tomts5-srv.bellnexxia.net (tomts5.bellnexxia.net [209.226.175.25]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f4GDSCA76893 for ; Wed, 16 May 2001 09:28:12 -0400 (EDT) (envelope-from msherman@projectile.ca) Received: from projectile.projectile.ca ([64.231.35.8]) by tomts5-srv.bellnexxia.net (InterMail vM.4.01.03.16 201-229-121-116-20010115) with ESMTP id <20010516132809.PCMK27183.tomts5-srv.bellnexxia.net@projectile.projectile.ca> for ; Wed, 16 May 2001 09:28:09 -0400 Received: from vomit.projectile.ca ([192.168.1.3] helo=vomit) by projectile.projectile.ca with smtp (Exim 3.22 #1 (Debian)) id 1501LU-0007iw-00 for ; Wed, 16 May 2001 09:28:08 -0400 From: "Marc Sherman" To: "pgsql-sql List" Subject: Select most recent record? Date: Wed, 16 May 2001 09:28:29 -0400 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2911.0) X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 Importance: Normal X-Archive-Number: 200105/159 X-Sequence-Number: 2976 Hi, I was hoping I could get some help with a select statement. I have a log table with three columns: id int4, timestamp datetime, value int4. For any given ID, there will be a large number of rows, with different timestamps and values. I'd like to select the newest (max(timestamp)) row for each id, before a given cutoff date; is this possible? The best I've been able to come up with is the rather ugly (and very slow): select * from log as l1 where timestamp in (select max(timestamp) from log where id=l1.id and timestamp<'2001-01-01' group by id); There must be a better way to do this; any tips? Thanks, - Marc