Received: from sss.pgh.pa.us (sss.pgh.pa.us [216.151.103.158]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f4GEoRA15385 for ; Wed, 16 May 2001 10:50:28 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.3/8.11.3) with ESMTP id f4GEoHR17283; Wed, 16 May 2001 10:50:17 -0400 (EDT) To: Andrew Perrin cc: Marc Sherman , pgsql-sql List Subject: Re: Select most recent record? In-reply-to: <3B02920A.B6340FC1@unc.edu> References: <17169.990023757@sss.pgh.pa.us> <3B02920A.B6340FC1@unc.edu> Comments: In-reply-to Andrew Perrin message dated "Wed, 16 May 2001 10:43:22 -0400" Date: Wed, 16 May 2001 10:50:17 -0400 Message-ID: <17280.990024617@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200105/168 X-Sequence-Number: 2985 Andrew Perrin writes: > Except that he wants max(timestamp) by id; perhaps a GROUP BY would > help, something like (untested): Oh, I'm sorry, I missed the "for each id" bit. > select max(timestamp) from log group by id; That gives you the max timestamp for each id, all right, but not the rest of the row in which the max timestamp occurs. I know of no good way to get that in standard SQL. If you don't mind being nonstandard, this is exactly the problem SELECT DISTINCT ON is meant for: select distinct on (id) * from log order by id, timestamp desc; See the SELECT reference page for more about this. regards, tom lane