Re: 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...

regards, tom lane