Re: [HACKERS] correlated subquery

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Is this a good example of a required correlated subquery:
>     SELECT f1.firstname, f1.lastname, f1.age
>     FROM friends f1
>     WHERE age = (
>                  SELECT MAX(age)
>                  FROM friends f2
>                  WHERE f1.state = f2.state
>                 )
>     ORDER BY firstname, lastname

> It finds the oldest person in each state.  HAVING can't do that, right?

Yes, I think you are right.  You could find the oldest age in each state
with a HAVING:
SELECT state, MAX(age) FROM friends GROUP BY state;

but I don't see any way to get the other attributes of the record(s)
matching that age, except by using a subselect.
        regards, tom lane