Finding non-distinct duplicate records in a table 1

[pyttviper]

I have to find duplicate records in a table where only two of the fields are the same between records.

EX

sequence F1 F2 F3 F4
1 05943 Service 2920 Tre
2 05943 Customer 2920 Hye

I would want to return only those records where F1 and F3 are the same and where F2 = Customer

Does this make any sense?

Dan0

 

[gregsimpson]

Select * from yourtable t1, yourtable t2
where t1.F1 = t2.F3 and
t1.F2 = "Customer" and
t2.F2 = "Customer"

I'm not sure if that is what your after. If not please post a bit more data in your example and also some desired results from the posted data.

 

[gregsimpson]

Having read it again, maybe your saying this

Select * from yourtable t1, yourtable t2
where t1.F1 = t2.F1 and
t1.F3 = t2.F3 and
t1.F2 = "Customer" and
t2.F2 = "Customer"

 

[PruSQLer]

Greg, I think your query will show every row as being a duplicate since it will match rows up to themselves in each instance of the table. DanO, if you can use temporary tables in your RDBMS, here's a solution that should work:

with temp_table
As (
select f1, f3
from table1
where f2 = 'Customer'
Group by f1, f3
Having count(*) > 1
)
select table1.*
from table1 t1,
temp_table temp
Where t1.f1 = temp.f1
and t1.f3 = temp.f3
and t1.f2 = 'Customer'

There's probably a way to do this via a subquery but I didn't come up with it.

 

[jimbopalmer]

at least in Oracle, you could add
and t1.rowid <> t2.rowid
to avoid joining the same row. I tried to remain child-like, all I acheived was childish.

 

[pyttviper]

All of these post have helped me come up with a solution to my problem.


Thank you


Dan0

 

[gregsimpson]

Prusqler,

I agree. I should have put on something to make sure the sequence number wasn't the same on each side of the join. In mitigation, I was still trying to understand the requirement. Out of interest do you think it would have worked if I had added sequence into the predicates as below, or have I still not quite grasped the problem.

Select * from yourtable t1, yourtable t2
where t1.F1 = t2.F1 and
t1.F3 = t2.F3 and
t1.F2 = &quot;Customer&quot; and
t2.F2 = &quot;Customer and
t1.sequence <> t2.sequence

Well done for sorting it. Enjoy your star.

 

[PruSQLer]

Greg, thanks for the star. My first one!

I've always been warned about using &quot;Not Equal&quot; in join criteria. And sure enough, it inflated the results in my example based on this problem from 72 rows to 470 rows. It may just be my example though. Perhaps the best solution from an ANSI standpoint is the query below, since I based my original query on a DB2 feature.

select *
from table1
where f2 in
(SELECT f1
from table1
WHERE f2 = 'Customer'
group by f1, f3
having count(*) > 1 )

AND f3 in
(SELECT f3
from table1
WHERE f2 = 'Customer'
group by f1, f3
having count(*) > 1 )
Order by f1, f3

Russ

 

[gregsimpson]

pyttviper

please post your solution to allow others to learn from the experience