Question:
There is a table with this structure
COL1 | COL2 | COL3
-----+------+-----
a | d | t
-----+------+-----
d | t | a
-----+------+-----
a | t | d
-----+------+-----
m | n | l
-----+------+-----
l | m | n
Records that can be obtained from other records by rearranging the values in the columns should be displayed only once. In this case, the answer would be:
COL1 | COL2 | COL3
-----+------+-----
a | d | t
-----+------+-----
l | m | n
It is necessary to solve the problem using standard SQL
Tell me where to start and how to organize the algorithm
Answer:
Here's a perversion I got for MS SQL:
select distinct max, middle, min
from
(
select
(select MAX(v) from (values (col1), (col2), (col3)) as value(v)) as max
,(
select *
from
(
select (case when col1 not in((select MAX(v) from (values (col1), (col2), (col3)) as value(v)),(select MIN(v) from (values (col1), (col2), (col3)) as value(v))) then col1 end) as m
union
select (case when col2 not in((select MAX(v) from (values (col1), (col2), (col3)) as value(v)),(select MIN(v) from (values (col1), (col2), (col3)) as value(v))) then col2 end) as m
union
select (case when col3 not in((select MAX(v) from (values (col1), (col2), (col3)) as value(v)),(select MIN(v) from (values (col1), (col2), (col3)) as value(v))) then col3 end) as m
) t where m is not null
) as middle
,(select MIN(v) from (values (col1), (col2), (col3)) as value(v)) as min
from #t
) temp
Where #t is the original table.