CODEWITHSUNDEEP
sqlduplicates
Pav3k
Pav3k

Reputation: 909

Remove duplicates based on two columns and reversed values

Let's say we have following table:

Select 
    'a' as col1,
    'b' as col2
union
Select 
    'a' as col1,
    'c' as col2
union
Select 
    'b' as col1,
    'a' as col2

It gives:

col1 col2
"a" "b"
"a" "c"
"b" "a"

How would you define duplicated row assuming that rows like ("a", "b") and ("b", "a") are the same.

So in fact result should be:

col1 col2
"a" "b"
"a" "c"

Upvotes: 1

Views: 509

Answers (2)

Phuri Chalermkiatsakul
Phuri Chalermkiatsakul

Reputation: 581

In Databricks, I can do something as follows and it works. It seems you need to have id to select distinct records by ignoring field order.

CREATE TABLE table_a AS
SELECT uuid() as id, col1, col2 FROM your_table;

SELECT a1.col1, a2.col2
FROM table_a a1
WHERE NOT EXISTS (SELECT 1
                  FROM table_a a2
                  WHERE a1.col1 = a2.col2 
                        AND a1.col2 = a2.col1
                        AND a1.id > a2.id);

Upvotes: 0

DhruvJoshi
DhruvJoshi

Reputation: 17126

judging by your sample data and results you could use a query like below

select 
distinct
case when t2.col1 is not null and t2.col1<t1.col1 then t2.col1 else t1.col1 end as col1,
case when t2.col2 is not null and t2.col2>t1.col2 then t2.col2 else t1.col2 end as col2
from yourtable t1 
left join yourtable t2
on t1.col1=t2.col2 and t1.col2=t2.col1

Upvotes: 1

Related Questions