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

Andreas




Thu, 30 Dec 1999 14:42:21 +0100
Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at>
AW: [HACKERS] correlated subquery
Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at> writes:
>> 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
>        GROUP BY f2.state, f1.firstname, f1.lastname, f1.age, f1.state
>        HAVING f1.age = max(f2.age)
>        ORDER BY firstname, lastname;

Hmm, yes, and you don't even need the GROUP BY state clauses.

But it's not really the same thing.  In particular, if you had two friends
with the same name and age, this would produce only one output record
for both, not two output records as Bruce's original query does.

That's neither likely nor a big problem in the hypothetical application,
but other applications needing this type of query might be more unhappy
about confusing similar records...

			regards, tom lane

Thu, 30 Dec 1999 10:05:30 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: AW: [HACKERS] correlated subquery 
[Charset iso-8859-1 unsupported, filtering to ASCII...]
> > 
> > 	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
>        GROUP BY f2.state, f1.firstname, f1.lastname, f1.age, f1.state
>        HAVING f1.age = max(f2.age)
>        ORDER BY firstname, lastname;

Yikes, you are right, and it works on PostgreSQL too.  I have added it
to my book.  Can anyone suggest queries that _must_ have subqueries? 
Seems table aliases can replace subqueries in most/all? cases?

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

Thu, 30 Dec 1999 12:07:35 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: AW: [HACKERS] correlated subquery