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, right?
Hmm, I'm explaining this really badly :). I should have defined a view
like you suggest to help simplify it. What I want is the pkeys (and
the field values) where no other pkey has that triple of field values.
That's why my earlier query does a group by the fields and then having
count(*) = 1. Also, FWIW, pkey1 is unique in its original table, but
not in the view, since some of the other tables are one-to-many.
select
v1.pkey1,
v1.field2,
v1.field3,
v1.field4,
v2.pkey1,
v2.field2,
v2.field3,
v2.field4,
from
view v1
join
view v2
on (
v1.field2=v2.field2 and
v1.field3=v2.field3 and
v1.field3=v2.field3 and
v1.pkey1<>v2.pkey
)
How does that work?
Won't this be a massive cross product of all pkey pairs that have the
same field values?
Here's what I'm currently using, in terms of your very helpful view:
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 the one that takes eight hours. :( Another way to express what
I want is this:
select v1.pkey1, v1.field2, v1.field3, v1.field4
from view as v1
where not exists
(select true from view as v2
where v1.field2 = v2.field2
and v1.field3 = v2.field3
and v1.field4 = v2.field4
and v1.pkey1 <> v2.pkey1);
That looks like a horrible nested loop, but I suppose I should try it
to make sure it is indeed slower then the previous query.
- John Burger
MITRE
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
