Michael EstrinOne Michael EstrinOne - 1 month ago 19
SQL Question

sql unique records puzzle

The puzzle is to select unique pairs. Syntax in the following example is for Mssql

declare @t table (a int, b int)

insert into @t (a,b) values (1,2)
insert into @t (a,b) values (2,1)
insert into @t (a,b) values (1,3)
insert into @t (a,b) values (3,1)
insert into @t (a,b) values (5,6)


select * from @t -- it outputs 5 records.


I need to get unique pairs only regardless of the order a,b, which should give me three records
(1,2),(1,3),(5,6)

I am out of ideas and will appreciate the help:)

Answer

one way (demo) would be

SELECT DISTINCT v.a,
                v.b
FROM   @t
       CROSS APPLY (VALUES(a,b),
                          (b,a)) v(a, b)
WHERE  v.a <= v.b 
Comments