Sample Data:
val1,val2 1,1 1,2 1,3 2,1 2,2 2,3 3,1 3,2 3,3 4,1
Excepted ouput:
val1,val2 1,2 1,3 2,3 4,1
The Logic :
Remove the same val permutations and retain the min val1 data.
e.g:
Remove 1,1
because they’re same.
Remove 2,1
because 1,2
exist…etc
Data SQL Script :
CREATE TABLE T (val1 int, val2 int) ; INSERT INTO T (val1, val2) VALUES (1, 1), (1, 2), (1, 3), (2, 1), (2, 2), (2, 3), (3, 1), (3, 2), (3, 3), (4, 1) ;
Advertisement
Answer
Here is one way to apply the two conditions:
select val1, val2 from mytable where val1 <> val2 --Remove 1,1 because they're same. and not exists -- Remove 2,1 because 1,2 exists, etc. ( select null from mytable other where other.val1 < other.val2 and other.val1 = mytable.val2 and other.val2 = mytable.val1 );