How to find duplicates pairs in MySQL

Question

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

See this page: http://stackoverflow.com/questions/688549/finding-duplicate-values-in-mysql — Mohammad Saberi, Dec 21 '11 at 19:15
http://stackoverflow.com/questions/8590010/delete-duplicate-records-without-creating-a-temporary-table — newtover, Dec 22 '11 at 12:50

score 3 · Answer 1 · answered Dec 21 '11 at 19:24

3

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.

answered Dec 21 '11 at 19:24

Dan Soap

10,114
1
40
49

It would be very interested to see the running time on your query against a JOIN, especially against a large table.+1 for the underdog query !!! – RolandoMySQLDBA Dec 26 '11 at 23:27
I say underdog because most would probably scoff a query not using a JOIN, but I like queries that come from thinking outside the box. – RolandoMySQLDBA Dec 26 '11 at 23:28

Andreas Wederbrand · Answer 2 · 2011-12-21T20:08:26.470

2

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)

edited Dec 21 '11 at 20:08

answered Dec 21 '11 at 19:13

Andreas Wederbrand

38,065
11
68
78

1

small update to your answer, I think better should add **`OR (yt1.id1 = yt2.id1 AND yt1.id2 = yt2.id2)`** – Siva Charan Dec 21 '11 at 19:24
You're right, I'll add it. It's not in the question but likely it should be added anyway. – Andreas Wederbrand Dec 21 '11 at 20:07

score 0 · Answer 3 · answered Oct 25 '16 at 22:17

0

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.

answered Oct 25 '16 at 22:17

rodrigo-silveira

12,607
11
69
123

fyi missing comma in concat() args. Should be `CONCAT(d1, '/', d2)` – grimmdude May 26 '20 at 19:53

How to find duplicates pairs in MySQL

3 Answers3