How to eliminate duplicate rows where a duplicate is defined by transposed values between 2 columns

Question

I want to eliminate any row in which the value of Column A appears in Column B along with the value of Column B appearing in Column A.

For example

id | column_A | column_B
------------------------
1  | quick    | brown
2  | quick    | fox
3  | brown    | quick
4  | lazy     | dog  
5  | fox      | quick

I am trying to get a result set

id | column_A | column_B
------------------------
1  | quick    | brown
2  | quick    | fox
4  | lazy     | dog

As you can see the rows with ids 3 & 5 are eliminate as, in the row with id = 3 the value of column_a = brown and column_b = quick which matches the transposed values of id = 1 where column_a = quick and column_b = brown. Similarly with the row with id = 2 eliminating the row where id = 5.

http://stackoverflow.com/questions/23103462/unique-constraint-on-two-fields-and-their-opposite — Marshall Tigerus, Jun 09 '16 at 18:59

score 0 · Answer 1 · answered Jun 09 '16 at 22:36

DECLARE @Tx TABLE (
     ID         INT IDENTITY
    ,column_A   NVARCHAR(20)
    ,column_B   NVARCHAR(20)
    )

INSERT INTO @Tx VALUES
     ('quick','brown')
    ,('quick','fox')
    ,('brown','quick')
    ,('lazy','dog')
    ,('fox','quick')

;WITH   RN
    AS (
        SELECT ID,
            CASE WHEN column_A < column_B THEN column_A + column_B
                 ELSE column_B + column_A END AS RNx
            FROM @Tx
        ),

        RO
    AS (
        SELECT ID, RNx, ROW_NUMBER() OVER (PARTITION BY RNx ORDER BY ID) AS RON
            FROM RN
        )

DELETE Tx
    FROM @Tx Tx
        LEFT JOIN RO
            ON Tx.ID = RO.ID AND RO.RON > 1
                WHERE RO.ID IS NOT NULL

SELECT * FROM @Tx

How to eliminate duplicate rows where a duplicate is defined by transposed values between 2 columns

1 Answers1