From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Chris Bitmead <chris(dot)bitmead(at)bigfoot(dot)com> |
Cc: | "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgreSQL(dot)org> |
Subject: | Re: [SQL] Finding the "most recent" rows |
Date: | 1999-04-23 00:45:06 |
Message-ID: | 20211.924828306@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Chris Bitmead <chris(dot)bitmead(at)bigfoot(dot)com> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Brett W. McCoy | 1999-04-23 01:03:22 | Re: [SQL] Changing the editor for the \e option |
Previous Message | Chris Bitmead | 1999-04-23 00:18:33 | Re: [SQL] SELECT TOP X -- part 2 -- parse error? |