Order Comparison in Oracle SQL

Question

I have two tables, t1, t2.

For instance: t1 is like this:

ID Ordinal
1    1
2    2
3    3
4    4

t2 is like this:

ID Ordinal
1    1
2    3
3    5
4    6

In this scenario, while I do the comparison of the ordinal, I want to return the order as "no Change", because if you look carefully, the actual order of those two tables are not changed.

but in a scenario like this: t2 is like this:

ID Ordinal
1    1
2    5
3    3
4    6

I would like to see the result as ID 2 and 3 are changed from T1.

Any thoughts?

Thanks!

1. List item

Answer 1

Try using the query below:

    SELECT  a.ID, a.Ordinal, b.Ordinal
      FROM  (
                        SELECT t1.*, rownum rnm 
                            FROM t1  
                        ORDER BY Ordinal
                    )a, 
                    (
                        SELECT t2.*, rownum rnm 
                            FROM t2
                        ORDER BY Ordinal
                    )b
     WHERE  a.ID = b.ID
--   AND a.rnm <> b.rnm //This deosn't work
     AND a.Ordinal <>  b.Ordinal

Edit: Updated the query to return the required result set

Answer 2

Try this:

SELECT T2.ID
FROM (SELECT ID, ROW_NUMBER() OVER (ORDER BY Ordinal) AS rn FROM T1) T1
JOIN (SELECT ID, ROW_NUMBER() OVER (ORDER BY Ordinal) AS rn FROM T2) T2
ON T1.ID = T2.ID 
WHERE T1.rn <> T2.rn