Re: using top-level aggregate values in subqueries

"Thomas F. O'Connell" <tfo(at)monsterlabs(dot)com> 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