"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
