If I a table:
X1 X2
1 2
2 1
3 2
I want to remove the duplicated pairs regardless of whether they are in X1 or X2 and to return either
X1 X2
1 2
3 2
or
X1 X2
2 1
3 2
Is there a way to do this?
If you have other columns and the pairs only appear once (in either direction):
select t.*
from t
where t.x1 <= t.x2
union all
select t.*
from t
where t.x1 > t.x2 and
not exists (select 1 from t t2 where t2.x1 = t.x2 and t2.x2 = t.x1);
Although not part of the SQL standard (which is what the sql tag refers to), most DBMS support the greatest() and least() functions:
select distinct least(x1, x2) as x1, greatest(x1,x2) as x2
from the_table;
Always select the smallest value first. Do UNION to remove duplicates.
select x, y from table where x <= y
union
select y, x from table where x > y
mysql, sql, oracle, I think your problem is related to a single tag?