I have the following scenario:
In a SQL-Table I have the following columns:
|----------------------------------------|-------------------------------------| | Col 1 | Col 2 | |----------------------------------------|-------------------------------------| | 4BAEFCAD-0B61-E911-B26B-005056872FC1 | 855757A6-0D61-E911-B26B-005056872FC1| |----------------------------------------|-------------------------------------| | 855757A6-0D61-E911-B26B-005056872FC1 | 4BAEFCAD-0B61-E911-B26B-005056872FC1| |----------------------------------------|-------------------------------------| | D2ADDEF8-A3A8-E911-B272-005056872FC1 | CED9DFD0-35A9-E911-B272-005056872FC1| |----------------------------------------|-------------------------------------|
The first two rows are kind of referencing to the same records because they contain the same values for row1 and row 2 but swapped.
Is there an sql-way of only retrieving one of those two colums?? so that in the end I receive the following result:
|----------------------------------------|-------------------------------------| | Col 1 | Col 2 | |----------------------------------------|-------------------------------------| | 4BAEFCAD-0B61-E911-B26B-005056872FC1 | 855757A6-0D61-E911-B26B-005056872FC1| |----------------------------------------|-------------------------------------| | D2ADDEF8-A3A8-E911-B272-005056872FC1 | CED9DFD0-35A9-E911-B272-005056872FC1| |----------------------------------------|-------------------------------------|
THX
Advertisement
Answer
A simple method is:
select col1, col2
from t
where col1 < col2
union all
select col1, col2
from t
where col2 > col1 and
not exists (select 1 from t t2 where t2.col2 = t.col1 and t2.col1 = t.col2);
This has the advantage of preserving the original rows. If you don’t care about the ordering:
select distinct (case when col1 < col2 then col1 else col2 end) as col1,
(case when col1 < col2 then col2 else col1 end) as col2
from t;