CODEWITHSUNDEEP
mysqlduplicates
Cyril Beslay
Cyril Beslay

Reputation:

How to find duplicates pairs in MySQL

I have a MySQL table like this:

| id1 | id2 |

| 34567 | 75879 | <---- pair1

| 13245 | 46753 |

| 75879 | 34567 | <---- pair2

| 06898 | 00013 |

with 37 000 entries.

What is the SQL Request or how can i identify duplicates pairs (like pair1 and pair2)?

Thanks

Upvotes: 4

Views: 3031

Answers (3)

rodrigo-silveira
rodrigo-silveira

Reputation: 13088

The original post is 1000 years old, but here's another form:

SELECT CONCAT(d1, '/' d2) AS pair, count(*) AS total
FROM your_table
GROUP BY pair HAVING total > 1
ORDER BY total DESC;

May or may not perform as well as the other suggested answers.

Upvotes: 0

Andreas Wederbrand
Andreas Wederbrand

Reputation: 40061

If you join the table with it self you can filter out the ones you need.

SELECT * 
  FROM your_table yt1,
       your_table yt2 
 WHERE (yt1.id1 = yt2.id2 AND yt1.id2 = yt1.id1)
    OR (yt1.id1 = yt2.id1 AND yt1.id2 = yt2.id2)

Upvotes: 2

Dan Soap
Dan Soap

Reputation: 10248

if you want to identify the duplicates and count them at the same time, you could use:

SELECT if(id1 < id2, id1, id2), if (id1 < id2, id2, id1), count(*)
  FROM your_table
 GROUP BY 1,2
HAVING count(*) > 1

This does not perform a join, which might be faster in the end.

Upvotes: 3

Related Questions