You may use ROW_NUMBER
here along with least/greatest logic:
WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY
CASE WHEN ColA <= ColB THEN ColA ELSE ColB END,
CASE WHEN ColA > ColB THEN ColA ELSE ColB END
ORDER BY ID) rn
FROM yourTable
)
SELECT ID, ColA, ColB
FROM cte
WHERE rn = 1;
Demo
Explanation:
The two CASE
expressions which appear in the partition clause of ROW_NUMBER
find the smallest and greatest of the two columns ColA
and ColB
, for each record. So, we partition on the pair of least/greatest of the two columns, and then retain the record for each pair having the smallest ID
value.
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…