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 );