Обсуждение: AW: [HACKERS] 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?

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

Andreas

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

[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