Sample Data:
x
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
);