Pfthb

I have table with:

A B
1 2
2 1

and i trying using sql command to get only one combination

A B
1 2

how can i do that?

A canonical way in standard SQL is:

select a, b
from t
where a < b
union all
select a, b
from t
where a > b and not exists (select 1 from t t2 where t2.a = t.b and t2.b = t.a);

Note that this assumes no duplicates or equal values. You can easily handle these using select distinct and <= comparisons. In my experience, this problem often arises when there are at most two rows per pair.

This preserves the original values. So, if you start with:

1 2
5 4

You will get that in the result set.

If you don't care about ordering, then many databases support least()/greatest():

select least(a, b) as a, greatest(a, b) as b
from t
group by least(a, b), greatest(a, b);

You can do the same thing with case expressions. Or, more simply as:

select distinct least(a, b) as a, greatest(a, b) as b
from t;

You just need to skip a row if it is a duplicate. 2, 1 and 1, 2 are duplicates of each other in which case we skip 2, 1 as a duplicate of 1, 2.

DECLARE @t TABLE (a INT, b INT);
INSERT INTO @t VALUES
(1, 1),
(1, 2),
(2, 1),
(3, 1);

SELECT DISTINCT a, b
FROM @t AS t
WHERE NOT EXISTS (
 SELECT 1
 FROM @t AS dup
 WHERE a = t.b AND b = t.a AND a < b
)

Result:

| a | b |
|---|---|
| 1 | 1 |
| 1 | 2 |
| 3 | 1 |