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
