CODEWITHSUNDEEP
javamysqloracle11g
Mayank Khurrana
Mayank Khurrana

Reputation: 15

SQL Distinct based on two COLUMNS and also Interchange of those two columns values should be considered as one row

SQL Distinct based on two COLUMNS and also Interchange of those two columns values should be considered as one row

As explained in the below example, Table col1, col2 and corresponding output:-

Table 

Col 1     Col 2
A           B
A           B
B           A
C           D
D           C
E           F

Output

Col1      Col2
A           B
C           D
E           F

Upvotes: 1

Views: 436

Answers (2)

user5683823
user5683823

Reputation:

Assumptions:

  • if a row like E F does not have its "mirror image" in the table, the row should be represented in the output as is. In other words, if the last row was F E instead of E F, it shouldn't be flipped; only the row F E, not its mirror image, should be in the output. This requirement makes the problem more interesting - otherwise it's much easier and a solution has already been provided.
  • col1 and col2 are non-nullable. If you may have nulls in the columns, you will need to give a more detailed of the requirement when nulls are in fact present.

Query and output:

with
     input_table ( col1, col2 ) as (
       select 'A', 'B' from dual union all
       select 'A', 'B' from dual union all
       select 'B', 'A' from dual union all
       select 'C', 'D' from dual union all
       select 'D', 'C' from dual union all
       select 'E', 'F' from dual union all
       select 'Z', 'H' from dual
     ),
     prep ( col1, col2, flag ) as (
       select a.col1, a.col2, case when b.col1 is null then 0 else 1 end
       from   input_table a left outer join input_table b
                            on a.col1 = b.col2 and a.col2 = b.col1
     )
select distinct
       case when flag = 0 then col1 else least   (col1, col2) end as col1,
       case when flag = 0 then col2 else greatest(col1, col2) end as col2
from   prep
;                   

COL1 COL2
---- ----
E    F
A    B
Z    H
C    D

Upvotes: 0

1000111
1000111

Reputation: 13519

Try with greatest and least along with distinct

SELECT 
 DISTINCT LEAST(col1,col2),
 GREATEST(col1,col2)
FROM your_table;

Demo Here

Upvotes: 5

Related Questions