How to drop reverse duplicates from a table?-CodePudding

Let's say I have a table like this,

Table A:

source
  a
  b
  c
  d

Table B:

destination
    b
    a
    d
    c

I have written this query to join two table,

with A as(
select row_number() over() idx, source from a 
),
B as (
select row_number() over() idx, destination from b
),
C as (
select A.source, B.destination from A join B on A.idx=B.idx
)

select * from C;

This returned this below table,

  source  destination
    a        b
    b        a
    c        d
    d        c

Now I want to remove reverse duplicates from it. I only want to keep one record a, b not b, a. Similarly I only want c, d not d, c.

Desired Output:

source    destination 
  a           b
  c           d

CodePudding user response：

from my merged table, how can I keep only unique records about source and destination? – user_12

DELETE t1.*
FROM merged_table t1
JOIN merged_table t2 ON t1.src = t2.dst
                    AND t1.dst = t2.src
                    AND t1.src > t2.src;

FIDDLE with some explanational queries.