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
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.
Example 1: Comparing rows of the same table. In the example, we are comparing the immediate rows to calculate the sales made on a day by comparing the amounts of two consecutive days. Syntax for inner join : SELECT column_name(s) FROM table1 t1 INNER JOIN table1 t2 on t1. column1 = t2.
Here's the SQL query to compare each row with previous row. In the above query, we join sales table with itself using an INNER JOIN condition g2.id=g1.id + 1 that allows you to compare each row with its previous row. Please note, this condition depends on the fact that our id column has consecutive numbers.
Compare SQL Server Data in Tables Using the Tablediff Tool This can be found in "C:\Program Files\Microsoft SQL Server\110\COM\" folder. This command line tool is used to compare tables. It also generates a script with the INSERT, UPDATE and DELETE statements to synchronize the tables.
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With