SQLite Compare Orders-CodePudding

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