SQL - Finding differences in row order of two tables

Question

I have two tables of ID's and dates and I want to order both tables by date and see those ids that are not in the same order

e.g. table_1

id   |  date
------------
A       01/01/09
B       02/01/09
C       03/01/09

table_2

id   |  date
------------
A       01/01/09
B       03/01/09
C       02/01/09

and get the results

 B
 C

Now admittedly I could just dump the results of an order by query and diff them, but I was wondering if there is an SQL-y way of getting the same results.

Edit to clarify, the dates are not necessarily the same between tables, it's just there to determine an order

Thanks

Answer 1

Is it not just the case of joining on the date and comparing the IDs are the same. This assumes that table_1 is the master sequence.

SELECT table_1.id
FROM
  table_1
INNER JOIN table_2
    on table_1.[date] = table_2.[date]
WHERE table_1.id <> table_2.id
ORDER BY table_1.id

Answer 2

ehm select id from table_1, table_2 where table_1.id = table_2.id and table_1.date <> table_2.date ?

Answer 3

Hi Colin,

if the dates are different in TABLE_1 and TABLE_2, you will have to join both tables on their rank. For exemple:

SQL> WITH table_1 AS (
  2     SELECT 'A' ID, DATE '2009-01-01' dt FROM dual UNION ALL
  3     SELECT 'B', DATE '2009-01-02' FROM dual UNION ALL
  4     SELECT 'C', DATE '2009-01-03' FROM dual
  5  ), table_2 AS (
  6     SELECT 'A' ID, DATE '2009-01-01' dt FROM dual UNION ALL
  7     SELECT 'C', DATE '2009-01-02' FROM dual UNION ALL
  8     SELECT 'B', DATE '2009-01-03' FROM dual
  9  )
 10  SELECT t1.ID
 11    FROM (SELECT ID, row_number() over(ORDER BY dt) rn FROM table_1) t1
 12   WHERE (ID, rn) NOT IN (SELECT ID,
 13                                 row_number() over(ORDER BY dt) rn
 14                            FROM table_2);

ID
--
B
C