How to drop reverse duplicates from a table?

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

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0