1

Topic: To select "unique" records

Dear colleagues, help to make request for sampling of "unique" records.
Initial data:

with an as (
select ' A ' a, ' B ' b
), b as (
select a, b from a
union
select b, a from a
union
select ' A ', ' A C'
)
select * from b

As a result  it is necessary to receive a dial-up

 a b
A B
A A C

Or

 a b
A A C
B A 

That is lines

 A, B 

and

 B, A 

to consider "identical", it is necessary to leave only one of them, (any).

2

Re: To select "unique" records

Uridian;
About today I already saw such request, whence it? smile

3

Re: To select "unique" records

From a head. Itself invented.))

4

Re: To select "unique" records

pivot and unpivot was useful

with
an as (
select ' A ' a, ' B ' b
)
,b as (
select a, b from a
union
select b, a from a
union
select ' A ', ' A C'
)
,t as (
select
row_number () over (order by (select null)) as rn_row, *
from b
)
,unp as (
select
rn_row
,lit
,row_number () over (partition by rn_row order by lit) as rn_col
from t unpivot (lit for [column] in (a, b)) as unp
)
select distinct piv. [1], piv. [2]
from unp pivot (min (lit) for rn_col in ([1], [2])) piv

5

Re: To select "unique" records

tashkafox wrote:

pivot and unpivot was useful

with
an as (
select ' A ' a, ' B ' b
)
,b as (
select a, b from a
union
select b, a from a
union
select ' A ', ' A C'
)
,t as (
select
row_number () over (order by (select null)) as rn_row, *
from b
)
,unp as (
select
rn_row
,lit
,row_number () over (partition by rn_row order by lit) as rn_col
from t unpivot (lit for [column] in (a, b)) as unp
)
select distinct piv. [1], piv. [2]
from unp pivot (min (lit) for rn_col in ([1], [2])) piv

, it is better you nail a computer hammer.

6

Re: To select "unique" records

tashkafox;
.

select a.*
from b a
LEFT JOIN b b
ON
b.b = a.a AND
b.a = a.b
WHERE a.a> a.b OR b.a IS NULL

7

Re: To select "unique" records

TaPaK;
and if columns will be more?

8

Re: To select "unique" records

tashkafox;
If columns will be more it will be the grandfather, instead of the grandmother.

9

Re: To select "unique" records

tashkafox wrote:

TaPaK;
and if columns will be more?

Well let's up how to select greatest of, 3 smile

10

Re: To select "unique" records

tashkafox wrote:

Ok and if columns will be more?

And if it is more and varied, that, for example, so:

with an as (
select ' A ' a, ' B ' b, 2 as c, 1.5 as d, getdate () as e, dateadd (day,-10, getdate ()) as f
), b as (
select a, b, c, d, e, f from a
union
select b, a, c, d, f, e from a
union
select a, b, c, d, f, e from a
union
select ' A ', ' A C ', 0, 0, dateadd (day,-1, getdate ()), getdate ()
)
select top (1) with ties
b.a, b.b, b.c, b.d, b.e, b.f
from
b cross apply
(
select
v as [*]
from
(
values
(cast (b.a as sql_variant));
(cast (b.b as sql_variant));
(cast (b.c as sql_variant));
(cast (b.d as sql_variant));
(cast (b.e as sql_variant));
(cast (b.f as sql_variant))
) t (v)
order by
v
for xml path (")
) c (x)
order by
row_number () over (partition by c.x order by c.x);

11

Re: To select "unique" records

Got out so, but looks it is verbose, (but  is not present):

with an as (
select ' A ' a, ' B ' b
), b as (
select * from a
union
select b a, a b from a
union
select ' A ', ' A C'
), c as (select case when a <b then an else b end a, case when a <b then b else an end b from b)
, d as (select *, row_number () over (partition by a, b order by a, b) as RowNmb from c)
select a, b from d where RowNmb = 1