Обсуждение: correlated subquery

Is this a good example of a required correlated subquery:
SELECT f1.firstname, f1.lastname, f1.ageFROM friends f1WHERE age = (             SELECT MAX(age)             FROM
friendsf2             WHERE f1.state = f2.state            )ORDER BY firstname, lastname
 

It finds the oldest person in each state.  HAVING can't do that, right?

--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

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

>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

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

Yikes, that would be painful.  Good point.  Fortunately, the data has
only one max person per state.

--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026