Re: Finding uniques across a big join - Mailing list pgsql-general

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