How to find duplicate pairs in MySQL

Question

I have a MySQL table as follows:

| id1 | id2 |

| 34567 | 75879 | <---- pair1

| 13245 | 46753 |

| 75879 | 34567 | <---- pair2

| 06898 | 00013 |

with 37,000 entries.

What is an SQL query, or how can I identify duplicate pairs (e.g., pairs 1 and pair2)?

thanks

Answer 1

if you want to identify duplicates and read them at the same time, you can 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 complete the connection, which may be faster at the end.

Answer 2

If you join a table with it, 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) 

Answer 3

The original post is 1000 years old, but here is a different 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 appear in the same way as the other proposed answers.