Individual values ​​that ignore column order

Question

I have a table similar to: -

+----+---+---+ | Id | A | B | +----+---+---+ | 1 | 1 | 2 | +----+---+---+ | 2 | 2 | 1 | +----+---+---+ | 3 | 3 | 4 | +----+---+---+ | 4 | 0 | 5 | +----+---+---+ | 5 | 5 | 0 | +----+---+---+ 

I want to remove all duplicate value pairs, no matter which column contains which value, for example. after any request I want to see: -

 +----+---+---+ | Id | A | B | +----+---+---+ | 1 | 1 | 2 | +----+---+---+ | 3 | 3 | 4 | +----+---+---+ | 4 | 0 | 5 | +----+---+---+ 

I would like to find a solution in Microsoft SQL Server (should work in <= 2005, although I would be interested in any solutions that rely on> = 2008 functions independently).

Also, note that A and B will be in the range of 1-100 (but this is not guaranteed forever. These are surrogate seed integer foreign keys, however the external table can grow up to several hundred lines).

I am wondering if I am missing some obvious solution here. The ones that happened all look pretty overloaded, although I think they are likely to work, for example: -

• The subquery returns a bit of the field with each bit corresponding to one of the identifiers, and uses this value to remove duplicates.
• Somehow, collapse, delete duplicates, and then do not open. It will probably be difficult.

Thanks in advance!

Answer 1

Try:

 select min(Id) Id, A, B from (select Id, A, B from DuplicatesTable where A <= B union all select Id, BA, AB from DuplicatesTable where A > B) v group by A, B order by 1 

Answer 2

Test data and sample below.

Basically, we make a self-connection with the criteria of OR , therefore either a = a or b = b OR a = b and b = a.

WHERE in the subquery gives the maximum exception for each pair.

I think this should work for three repetitions as well (note I added the 6th line).

 DECLARE @t table(id int, a int, b int) INSERT INTO @t VALUES (1,1,2), (2,2,1), (3,3,4), (4,0,5), (5,5,0), (6,5,0) SELECT * FROM @t WHERE id NOT IN ( SELECT a.id FROM @ta INNER JOIN @tb ON (aa=ba AND ab=bb) OR (ab=ba AND aa = bb) WHERE a.id > b.id) 

Answer 3

It is not 100% verified, and I am sure that it can be removed, but it gives the desired result:

 DECLARE @T TABLE (id INT IDENTITY(1,1), A INT, B INT) INSERT INTO @T VALUES (1,2), (2,1), (3,4), (0,5), (5,0); SELECT * FROM @T WHERE id IN (SELECT DISTINCT MIN(id) FROM (SELECT id, a, b FROM @T UNION ALL SELECT id, b, a FROM @T) z GROUP BY a, b)