Re: Finding uniques across a big join

Bruno Wolff III wrote:

>> That changes the semantics of what I want. If I group by personID
>> above, then every FOUR-way combo is of course unique. What I'd like
>> to
>> do is group by the three attributes, and select for personID as well.
>> But of course you can't select for columns you haven't grouped by.
>
> Assuming that personID is an ordered type, you can select max(personID)
> in the GROUP BY and save the join at the end.

I'm not sure what this means - do you mean:

select p2.eyeColor, p2.hairColor, p2.skinColor
from persons as p2
group by max(p2.personID), p2.eyeColor, p2.hairColor, p2.skinColor
having count(*) = 1;

I don't know what that does. If you mean:

select max(p2.personID), p2.eyeColor, p2.hairColor, p2.skinColor
from persons as p2
group by p2.personID, p2.eyeColor, p2.hairColor, p2.skinColor
having count(*) = 1;

then I don't think that works either - if I include personID in the
GROUP BY, then the COUNT doesn't do what I want, right? I just want
uniques wrt the three attribute fields. If I group by personID, then
personID counts towards uniqueness.

Thanks for all the suggestions, folks.

- John Burger
MITRE