Re: Finding uniques across a big join

From: "John D(dot) Burger" <john(at)mitre(dot)org>
To: PostgreSQL general <pgsql-general(at)postgresql(dot)org>
Subject: Re: Finding uniques across a big join
Date: 2005-12-01 12:43:49
Message-ID: 1a8c3e176a2532e639ffe4020c7caabd@mitre.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andrew Sullivan 2005-12-01 12:45:38 Re: undefined behaviour for sub-transactions?
Previous Message William Leite Araújo 2005-12-01 12:25:48 alter table schema on 8.0.X