Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [206.210.65.6]) by hub.org (8.9.3/8.9.1) with ESMTP id LAA75670 for ; Thu, 22 Apr 1999 11:30:06 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id LAA17968 for ; Thu, 22 Apr 1999 11:29:31 -0400 (EDT) To: "pgsql-sql@postgresql.org" Subject: Re: [SQL] Finding the "most recent" rows In-reply-to: Your message of Thu, 22 Apr 1999 14:45:13 +0000 <371F35F9.607AF4B7@bigfoot.com> Date: Thu, 22 Apr 1999 11:29:31 -0400 Message-ID: <17966.924794971@sss.pgh.pa.us> From: Tom Lane Chris Bitmead writes: > Julian Scarfe wrote: >> 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. > > Try > SELECT the_place, max(the_time) FROM the_place GROUP BY the_place; But I'll bet he wants the whole row containing the max time, not just the place and time columns. I've run into similar problems and never felt like I had a clean solution, either. You could do something like SELECT * FROM table AS t1 WHERE NOT EXISTS(SELECT * FROM table AS t2 WHERE t2.place = t1.place AND t2.time > t1.time); but this is ugly, and probably horribly inefficient as well. (It might not be unacceptably slow if the table has indexes on place and time, but it sure looks like a brute-force approach.) What you'd really like is something like a SELECT DISTINCT with a user- specifiable row comparison operator; then you'd just "ORDER BY place, time" and make a comparator that discards all but the last row for each place value. Hmm ... a little experimentation suggests that SELECT DISTINCT ON place * FROM table ORDER BY place, time DESC; might do the right thing. It *seems* to select the first row for each value of place. I've never seen a spec for this feature, however, so I'm not sure if it's reliable or not... regards, tom lane