Compare different orders of the same table

Question

I have this following scenario, a table with these columns:

table_id|user_id|os_number|inclusion_date

In the system, the os_number is sequential for the users, but due to a system bug some users inserted OSs in wrong order. Something like this:

table_id | user_id | os_number | inclusion_date
-----------------------------------------------
1        | 1       | 1         | 2015-11-01
2        | 1       | 2         | 2015-11-02
3        | 1       | 3         | 2015-11-01

• Note the os number 3 inserted before the os number 2

What I need:

Recover the table_id of the rows 2 and 3, which is out of order.

I have these two select that show me the table_id in two different orders:

select table_id from table order by user_id, os_number

select table_id from table order by user_id, inclusion_date

I can't figure out how can I compare these two selects and see which users are affected by this system bug.

Answer 1

Your question is a bit difficult because there is no correct ordering (as presented) -- because dates can have ties. So, use the rank() or dense_rank() function to compare the two values and return the ones that are not in the correct order:

select t.*
from (select t.*,
             rank() over (partition by user_id order by inclusion_date) as seqnum_d,
             rank() over (partition by user_id order by os_number) as seqnum_o
      from t
     ) t
where seqnum_d <> seqnum_o;