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 f3O2KZ371917 for ; Mon, 23 Apr 2001 22:20:35 -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 f3O2KTR25098; Mon, 23 Apr 2001 22:20:29 -0400 (EDT) To: "Thomas F. O'Connell" cc: pgsql-sql@postgresql.org Subject: Re: using top-level aggregate values in subqueries In-reply-to: <3AE4C818.5000804@monsterlabs.com> References: <3AE4C818.5000804@monsterlabs.com> Comments: In-reply-to "Thomas F. O'Connell" message dated "Mon, 23 Apr 2001 19:26:00 -0500" Date: Mon, 23 Apr 2001 22:20:29 -0400 Message-ID: <25095.988078829@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200104/366 X-Sequence-Number: 2724 "Thomas F. O'Connell" writes: > select f.id > from foo f, ola o > where f.id = ( > select max( b.id ) > from bar b > where b.bling = "i kiss you!" > ) > and o.id != ( > select max( b.id ) > from bar b > where b.bling = "i kiss you!" > ) > is there some way to grab the value returned by the subquery in the > superquery and use the value instead of running the subquery twice? In 7.1, perhaps something like this would do: select f.id from foo f, ola o, (select max( b.id ) as max from bar b where b.bling = "i kiss you!") ss where f.id = ss.max and o.id != ss.max In prior versions you'd have to fake it by selecting the subquery result into a temp table beforehand. > i'm not looking for an optimized version of my example While it's not a general solution, there's always transitivity: select f.id from foo f, ola o where f.id = ( select max( b.id ) from bar b where b.bling = "i kiss you!" ) and o.id != f.id regards, tom lane