Let's say I have a table with two columns a1
and a2
.
I need to check if the sequence order of this table is the same, when I order it by a1
and if I order it by a2
.
Select a1, a2 from Test order by a1
a1 | a2 |
---|---|
1 | 2 |
2 | 1 |
3 | 3 |
Select a1, a2 from Test order by a2
a1 | a2 |
---|---|
2 | 1 |
1 | 2 |
3 | 3 |
Is it somehow possible to check if both results are actually the same, respecting the order of each output?
I tried something like:
With order_1 as (Select * from Test order by a1),
order_2 as (Select * from Test order by a2)
Select * from order_1 except Select * from order_2
This should yield something like:
a1 | a2 |
---|---|
2 | 1 |
1 | 2 |
But this doesn't work, as it apparently doesn't respect the order.
CodePudding user response:
We can use ROW_NUMBER() OVER ( ORDER BY
in a CTE to find the entries which are not in the same position when we use the 2 different keys to sort.
Here we have inversed 3 and 4.
create table sorter ( a1 int, a2 int);
insert into sorter values (1,100),(2,200),(3,400),(4,300),(5,500);
with cte as ( select a1, a2, row_number() over (order by a1) rna1, row_number() over (order by a2) rna2 from sorter) select a1, a2 from cte where rna1 <> rna2;
a1 | a2 -: | --: 3 | 400 4 | 300
db<>fiddle here
CodePudding user response:
Use ROW_NUMBER()
window function twice for each of the columns a1
and a2
in a cte and then a self join of the cte to filter out the matching rows:
WITH cte AS (
SELECT *,
ROW_NUMBER() OVER (ORDER BY a1) rn_a1,
ROW_NUMBER() OVER (ORDER BY a2) rn_a2
FROM Test
)
SELECT c1.a1, c1.a2
FROM cte c1 INNER JOIN cte c2
ON c1.rn_a1 = c2.rn_a2 AND (c1.a1, c1.a2) <> (c2.a1, c2.a2)
ORDER BY c1.a1;
See the demo.
Results:
a1 | a2 |
---|---|
1 | 2 |
2 | 1 |