>
> 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