How to check if the results of two ordered SQL queries match?

Question

Here is the problem: I have two columns in the table that should be in the same order for each group. I want to verify that this is true for existing data before I make this assumption in the future.

Some sample data:

gid c1 c2 1 1 5 1 2 7 2 1 22 2 2 38 2 3 40 3 1 4 3 2 8 3 3 2 

As you can see, if I were to use select * from table t where t.gid = 1 order by c1
the result will be identical and ordered in exactly the same way as select * from table t where t.gid = 1 order by c2 .
The results will also match gid = 2. However, if I did the same with gid = 3, the order would no longer match. because in this case the order of c2 has a different result from ordering on c1 .

I want to check the entire table to ensure that ordering by any of these columns will have the same result for each gid value. Oracle MINUS will not work because it does not allow order by in subqueries. My first attempt, which does not work due to MINUS restrictions, was:

 select gid, c1, c2 from table order by gid, c1, c2 minus select gid, c1, c2 from table order by gid, c2, c1; 

I am using oracle 11g if this helps with the answer.

Answer 1

You can find the difference in order in the same record by assigning row_number .

 with ordering as ( select t.*, row_number() over (partition by gid order by c1) order_c1, row_number() over (partition by gid order by c2) order_c2 from t ) select * from ordering where order_c1 <> order_c2 

Answer 2

An interesting problem, you do not have Oracle, but Something like

 Select gid,c1,c2,row_number() as rownum from ordering order by gid,c1,c2 join (select gid,c1,c2,row_number() as otherrownum from ordering order by gid,c2,c1) otherOrdering On rownum = otherrownum and ((ordering.gid <> otherOrdering.gid) Or (ordering.c1 <> otherOrdering.c1) or (ordering.c2 <> otherOrdering.c2)) 

If it returns something, your guess is incorrect. Or you can change <> to = and Or to AND, it should return the same number of rows as in the table.

I think anyway.

Answer 3

I am not sure about the equivalent of EXCEPT for Oracle. But this works fine on MS SQL Server.