CODEWITHSUNDEEP
sql-serverduplicatesself-join
abahr
abahr

Reputation: 33

Finding duplicate records with different IDs within a table

I have a table as below:

ID     Product#   Service#   ServiceDate
1      100        122        2017-01-02
2      100        124        2017-03-02
3      122        133        2017-04-02        
100    100        122        2017-05-02

I need to find the records that have the same product# and service# but different IDs. For this, I wrote the code below:

Select * 
FROM MyTable as M1 Inner join 
MyTable as M2 on 
M1.Product#=M2.Product# and M1.Service#=M2.Service# and M1.ID!=M2.ID

However, I get duplicate results as such:

ID   Product# Service#  ServiceDate  ID   Product#  Service#  ServiceDate
1    100      122       2017-01-02   100  100       122       2017-05-02 
100  100      122       2017-05-02   1    100       122       2017-01-02

Any idea how to eliminate these duplicate rows? I need to see a result as such:

  ID   Product# Service#  ServiceDate  ID   Product#  Service#  ServiceDate
  1    100      122       2017-01-02   100  100       122       2017-05-02

Upvotes: 1

Views: 295

Answers (2)

Eli
Eli

Reputation: 2608

Try the following: 

Select  * 
FROM MyTable as M1 
Inner join MyTable as M2 on M1.Product#=M2.Product# and M1.Service#=M2.Service# and M1.ID!=M2.ID
where m1.id < m2.id

Explanation: Your example shows both sides of each coin; by limiting it to having one of the ID's being less than the other, you'll automatically have just half of the records, effectively getting you all unique combinations.

Bonus: For fun, I tried to add one more duplicate row to your sample data set, and it worked just as expected.

Upvotes: 1

Mat Richardson
Mat Richardson

Reputation: 3606

If you're wanting to return just two rows without the duplicate columns, replace 

Select *

with 

Select M1.*

Upvotes: 0

Related Questions