AW: [HACKERS] 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?

Having can do that particular case: (e.g. Informix)

SELECT f1.firstname, f1.lastname, f1.age
FROM friends f1, friends f2
WHERE f1.state = f2.state
GROUP BY f2.state, f1.firstname, f1.lastname, f1.age, f1.state
HAVING f1.age = max(f2.age)
ORDER BY firstname, lastname;

Andreas