Let’s say I have a table like this,
source a b c d
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.
source destination a b c d
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.
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