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