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?

-- 
  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, Pennsylvania 19026

Wed, 29 Dec 1999 17:36:39 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
correlated subquery
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

Wed, 29 Dec 1999 18:18:07 -0500
Tom Lane <tgl@sss.pgh.pa.us>
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

Wed, 29 Dec 1999 18:59:18 -0500
sszabo@bigpanda.com
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.

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, Pennsylvania 19026

Wed, 29 Dec 1999 19:44:07 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] correlated subquery