SQL Match records in any order-CodePudding

Given a table like

OBJECTID,UID,FID1,FID2
1,Record1,00000494e1f3,00000494e1f3
2,Record2,00000494e1ed,00000494e1ed
3,Record3,eff9df49d9ec,6d1f58545043
4,Record4,6d1f58545043,eff9df49d9ec
5,Record5,37fce22b2bb5,7fce22b2bb5
6,Record6,00000494e1ef,00000494e1ef

We can see that in records 3 and 4 the FID1 and FID2 values are the same but just in a different order.

I can simply concatenate the FID1 and FID2 values and then run unique on that column but this won't give us case where FID1/2 are the same but just in a different order.

See https://www.db-fiddle.com/f/gp3vYGhB9cgYUukcEwM3K3/1

How can I find all records where the FID1 and FID2 values are the same but just in a different order?

CodePudding user response：

This will do it: join the table to itself, and filter out the rows you are interested in (the FIDs are the same but in different order, and the rows are not one and the same row):

SELECT t1.*
FROM Test t1
JOIN Test t2
  ON t1.OBJECTID != t2.OBJECTID
  AND t1.r_FID1 = t2.r_FID2
  AND t2.r_FID1 = t1.r_FID2;

If you only want one of the duplicate rows, you can e.g. select the one where FID1 is larger than FID2 (the other row will obviously have this reversed):

SELECT t1.*
FROM Test t1
JOIN Test t2
  ON t1.OBJECTID != t2.OBJECTID
  AND t1.r_FID1 = t2.r_FID2
  AND t2.r_FID1 = t1.r_FID2
  AND t1.r_FID1 > t1.r_FID2;

PS: Thank you for the fiddle!

CodePudding user response：

Use IF and CONCAT in the select also can help you

SELECT hash
FROM(
select 
*, IF(r_FID1 > r_FID2, CONCAT(r_FID1,'_', r_FID2), CONCAT(r_FID2,'_', r_FID1)) `hash`
from Test
) t 
GROUP BY hash