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 UAA41164 for ; Thu, 22 Apr 1999 20:46:31 -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 UAA20213; Thu, 22 Apr 1999 20:45:07 -0400 (EDT) To: Chris Bitmead cc: "pgsql-sql@postgresql.org" Subject: Re: [SQL] Finding the "most recent" rows In-reply-to: Your message of Thu, 22 Apr 1999 23:57:32 +0000 <371FB76C.65A146D7@bigfoot.com> Date: Thu, 22 Apr 1999 20:45:06 -0400 Message-ID: <20211.924828306@sss.pgh.pa.us> From: Tom Lane Chris Bitmead writes: > Clever. But why doesn't this work.... > select title, summary, time from story t where time = (select > max(s.time) from story s GROUP BY s.title); > ERROR: parser: Subselect has too many or too few fields. A subselect used in an expression has to return exactly one value; yours will return as many tuples as there are distinct titles. I think you meant select title, summary, time from story t where time = (select max(s.time) from story s WHERE s.title = t.title); Here the subselect should give a single result each time it's executed. Unfortunately, it's gonna be executed once for each tuple scanned by the outer select :-( regards, tom lane