Re: Finding uniques across a big join

["John D. Burger" <john@xxxxxxxxx>]

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