AW: AW: [HACKERS] correlated subquery

> Zeugswetter Andreas SB <ZeugswetterA(at)wien(dot)spardat(dot)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...

Yes, it only gives the same result, if "f1" has some sort of primary key
(e.g. oid),
that can be put into the group by clause.

Andreas