Re: [HACKERS] correlated subquery

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

I'm assuming that this is for the book... If so, you might want to also
note that this query can return more people than there are states if
multiple people in the same state have the maximum age for that state.

I'm not sure how deeply you are going into this, but getting only one
person per state looks like it might be fairly painful... You might be
able cheat if there was only one field besides age and state in the output
using group by and an aggregate.

Stephan Szabo