I have some data in a MS SQL Server database. Sample data is:
Sl | ColumnA | ColumnB |
---|---|---|
1 | 111 | 112 |
2 | 112 | 111 |
3 | 113 | 114 |
4 | 114 | 113 |
5 | 115 | 116 |
6 | 116 | 115 |
Now I need result like.
ColumnA | ColumnA |
---|---|
111 | 112 |
113 | 114 |
115 | 116 |
Notice here:
In Sl 1 number Column A = Sl 2 Column B
and Sl 1 number Column B = Sl 2 Column A
Same as:
In Sl 3 number Column A = Sl 4 Column B
and Sl 3 number Column B = Sl 4 Column A
Advertisement
Answer
One approach is to use CASE
expressions to find the least/greatest value in each record:
x
SELECT DISTINCT
CASE WHEN ColumnA < ColumnB THEN ColumnA ELSE ColumnB END AS ColumnA,
CASE WHEN ColumnA >= ColumnB THEN ColumnA ELSE ColumnB END AS ColumnB
FROM yourTable
ORDER BY 1;
Demo
Note that in certain other databases (such as MySQL and Postgres), there are formal LEAST
and GREATEST
functions, which can simplify and eliminate the need for the bulky CASE
expressions used above.