Greg Stark wrote:
select max(personid) as personid, eyecolor, haircolor, skincolor
from persons
group by eyecolor, haircolor, skincolor
having count(*) = 1
Aha, I understand Bruno's suggestion now! I was actually trying to
think of some way of using an aggregate on personid, but couldn't
"John D. Burger" <[EMAIL PROTECTED]> writes:
> 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 thi
John D. Burger napisał(a):
>
> select v1.pkey1, v1.field2, v1.field3, v1.field4
> from view as v1
> join
> (select v2.field1, v2.field2, v2.field3
> from view as v2
> group by v2.field2, v2.field3, v2.field4
> having count(*) = 1)
> using (field2, field3, field4);
>
> This is
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
On Wed, Nov 30, 2005 at 20:44:30 -0500,
"John D. Burger" <[EMAIL PROTECTED]> 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
Scott Marlowe wrote:
Won't this be a massive cross product of all pkey pairs that have the
same field values?
Yes, assuming there are a lot of them. OTOH, if there are only a few
duplicates you're looking for...
I'm not looking for duplicates, I'm looking for uniques - note the
Subject lin
On Wed, Nov 30, 2005 at 01:29:17PM -0500, John D. Burger wrote:
> Jim C. Nasby wrote:
>
> >It will probably be a win to come up with a list of potential records
> >from each table, instead of after doing the 3-way join. so something
> >like:
> >
> >(SELECT gazPlaceID FROM gazPlaces GROUP BY featur
On Wed, 2005-11-30 at 16:27, John D. Burger wrote:
> Scott Marlowe wrote:
>
> > select
> > v1.pkey1,
> > v1.field2,
> > v1.field3,
> > v1.field4,
> > v2.pkey1,
> > v2.field2,
> > v2.field3,
> > v2.field4,
> > from
> > view v1
> > join
> > view v2
> > on (
>
Scott Marlowe wrote:
OK, let's assume that the basic part of it, before the group by, has
been put into a view, so we can then do:
select pkey1, field2, field3, field4 from view;
And we know that pkey1 is unique, but we want the records where pkey1
is
the only thing different between them, r
On Tue, 2005-11-29 at 20:58, John D. Burger wrote:
> I could use some help with the following:
>
> I have a database of geographic entities with attributes spread across
> several tables. I need to determine which entities are unique with
> respect to some of those attributes. I'm using the fo
On Wed, Nov 30, 2005 at 01:20:19PM -0500, John D. Burger wrote:
> >select p1.gazPlaceID
> > from gazPlaces as p1
> > join gazNamings as n1 using (gazPlaceID)
> > join gazContainers as c1 using (gazPlaceID)
> > group by p1.gazPlaceID, p1.featureType, n1.placeNameID,
> >c1.contai
Jim C. Nasby wrote:
It will probably be a win to come up with a list of potential records
from each table, instead of after doing the 3-way join. so something
like:
(SELECT gazPlaceID FROM gazPlaces GROUP BY featureType HAVING
count(*)=1)
JOIN
(SELECT ...)
Hmm, not sure I understand. Joini
On Nov 30, 2005, at 01:55, Martijn van Oosterhout wrote:
On Tue, Nov 29, 2005 at 09:58:49PM -0500, John D. Burger wrote:
I could use some help with the following:
I have a database of geographic entities with attributes spread across
several tables. I need to determine which entities are uni
It will probably be a win to come up with a list of potential records
from each table, instead of after doing the 3-way join. so something
like:
(SELECT gazPlaceID FROM gazPlaces GROUP BY featureType HAVING count(*)=1)
JOIN
(SELECT ...)
If you post the output of explain (or explain analyze is eve
On Tue, Nov 29, 2005 at 09:58:49PM -0500, John D. Burger wrote:
> I could use some help with the following:
>
> I have a database of geographic entities with attributes spread across
> several tables. I need to determine which entities are unique with
> respect to some of those attributes. I'm
I could use some help with the following:
I have a database of geographic entities with attributes spread across
several tables. I need to determine which entities are unique with
respect to some of those attributes. I'm using the following query:
select p2.gazPlaceID from
(select p1.feat
16 matches
Mail list logo
