Re: using top-level aggregate values in subqueries

"Thomas F. O'Connell" <tfo@monsterlabs.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 bwhere b.bling = "i kiss you!"
)
and o.id != f.id
        regards, tom lane