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
GROUPBY f2.state, f1.firstname, f1.lastname, f1.age, f1.state      HAVING f1.age = max(f2.age)      ORDER BY firstname,
lastname;

Andreas