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
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 |