Received: from tomts13-srv.bellnexxia.net (tomts13.bellnexxia.net [209.226.175.34]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f4GEqFA16188 for ; Wed, 16 May 2001 10:52:15 -0400 (EDT) (envelope-from msherman@projectile.ca) Received: from projectile.projectile.ca ([64.231.35.8]) by tomts13-srv.bellnexxia.net (InterMail vM.4.01.03.16 201-229-121-116-20010115) with ESMTP id <20010516145211.RESN25498.tomts13-srv.bellnexxia.net@projectile.projectile.ca> for ; Wed, 16 May 2001 10:52:11 -0400 Received: from vomit.projectile.ca ([192.168.1.3] helo=vomit) by projectile.projectile.ca with smtp (Exim 3.22 #1 (Debian)) id 1502ep-0007r0-00 for ; Wed, 16 May 2001 10:52:11 -0400 From: "Marc Sherman" To: "pgsql-sql List" Subject: RE: Select most recent record? Date: Wed, 16 May 2001 10:52:31 -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 In-Reply-To: <17169.990023757@sss.pgh.pa.us> Importance: Normal X-Archive-Number: 200105/167 X-Sequence-Number: 2984 From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > > "Marc Sherman" writes: > > I'd like to select the newest (max(timestamp)) row for each id, > > before a given cutoff date; is this possible? > > select * from log order by timestamp desc limit 1; Heh. I obviously simplified my situation too much. This is closer to what I've really got: create table user (userid int4 primary key, groupid int4); create table log (userid int4, timestamp datetime, value int4); I need to select sum(value) for each group, where the values chosen are the newest log entry for each group member that is before a cutoff date (April 1, 2001). Here's what I'm currently using: select user.groupid, sum(l1.value) from log as l1, user where user.userid=log.userid and log.timestamp in ( select max(timestamp) from log where log.timestamp<'2001-04-01' and log.userid=l1.userid) group by user.groupid; When I first posted, this was _very_ slow. I've since improved it by adding an index on log(userid,timestamp) - now it's just slow. If anyone knows how to make it faster, I'd appreciate it. - Marc