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