Re: Finding uniques across a big join

Greg Stark wrote:

> select max(personid) as personid, eyecolor, haircolor, skincolor
> from persons
> group by eyecolor, haircolor, skincolor
> having count(*) = 1

Aha, I understand Bruno's suggestion now! I was actually trying to
think of some way of using an aggregate on personid, but couldn't
figure it out. Of course max does just what I want in this case, since
max on one value gives that value - min and some other aggregate
functions would work too. Very clever!

On my actual problem, where "persons" is actually three joined tables,
my original query took eight hours. The new query, modeled after the
above, runs in almost exactly a tenth of the time.

Thanks for all the suggestions!

- John D. Burger
MITRE