Skip to content
Advertisement

Remove same permutations from different multiple columns

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

SQL Server 2012 | db<>fiddle

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
);
User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement