Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Compare different orders of the same table

Tags:

sql

postgresql

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.

like image 953
Wellington Zanelli Avatar asked Nov 30 '15 12:11

Wellington Zanelli


People also ask

How can I compare two records in the same table in SQL?

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.

How will you compare successive rows within the same table in mysql?

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.

How can I find the difference between two tables in SQL Server?

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.


1 Answers

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;
like image 116
Gordon Linoff Avatar answered Oct 01 '22 07:10

Gordon Linoff