> 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
> 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
that can be put into the group by clause.
pgsql-hackers by date
|Next:||From: Vadim Mikheev||Date: 1999-12-31 12:50:23|
|Subject: Happy New Year!|
|Previous:||From: Bruce Momjian||Date: 1999-12-31 06:08:01|
|Subject: When are subqueries needed|