Select a record only if it has a lower value

Question

I work with mysql and I have never encountered such a big problem. Hope you can help.

I have 1 table called Reports:

ID SerialNumber Remain_Toner_Black 7099 Z5UEBJAC900002Y 37 7281 Z5UEBJAC900002Y 36 7331 Z5UEBJAC900002Y 100 7627 8Z37B1DQ100105N 58 7660 8Z37B1DQ100105N 57 5996 CND8DDM2FH 83 5971 CND8DDM2FH 83 7062 3960125290 0 7088 3960125290 93 7100 3960125290 100 

Now I want to be able to select records from a table where Remain_Toner_Black is higher than Remain_Toner_Black of the previous row in the table with the same SerialNumber (by the previous, I mean a lower identifier with the same SerialNumber).

For the above entries, I want to get the following results:

 ID SerialNumber Remain_Toner_Black_Before Remain_Toner_Black_After 7331 Z5UEBJAC900002Y 36 100 7088 3960125290 0 93 7100 3960125290 93 100 

Answer 1

 SELECT a.ID, a.SerialNumber, b.Remain_Toner_Black BeforeCount, a.Remain_Toner_Black AfterCount FROM ( SELECT A.ID, A.SerialNumber, A.Remain_Toner_Black, ( SELECT COUNT(*) FROM tableName c WHERE c.SerialNumber = a.SerialNumber AND c.ID <= a.ID) AS RowNumber FROM TableName a ) a INNER JOIN ( SELECT A.ID, A.SerialNumber, A.Remain_Toner_Black, ( SELECT COUNT(*) FROM tableName c WHERE c.SerialNumber = a.SerialNumber AND c.ID <= a.ID) AS RowNumber FROM TableName a ) b ON a.SerialNumber = b.SerialNumber AND a.RowNumber = b.RowNumber + 1 WHERE b.Remain_Toner_Black < a.Remain_Toner_Black 

• SQLFiddle Demo

OUTPUT

 ╔══════╦═════════════════╦═════════════╦════════════╗ ║ ID ║ SERIALNUMBER ║ BEFORECOUNT ║ AFTERCOUNT ║ ╠══════╬═════════════════╬═════════════╬════════════╣ ║ 7331 ║ Z5UEBJAC900002Y ║ 36 ║ 100 ║ ║ 7088 ║ 3960125290 ║ 0 ║ 93 ║ ║ 7100 ║ 3960125290 ║ 93 ║ 100 ║ ╚══════╩═════════════════╩═════════════╩════════════╝ 

SHORT DESCRIPTION

What the request does above, it generates a serial number that mimics ROW_NUMBER() in other RDBSs for each SerialNumber , sorted by ID in ascending order.

Then the two subqueries are connected via SerialNumber and the generated serial number. According to the generated number, the value in the first subquery must be equal to plus one of the values ​​in the second subquery to get the amount of toner at the next level.

Answer 2

MySQL (or, indeed, most other RDBMSs) does not allow easy comparisons between rows.

To make such a query, you must either make a very expensive join by comparing one version of the table with several rows of another version of the table, or build a complex expression using custom variables.

For example (with user variables):

 SELECT ID, SerialNumber, @x Remain_Toner_Before, @x := Remain_Toner_Black AS Remain_Toner_After FROM Reports, (SELECT @x := -4) x WHERE Remain_Toner_Black > @x AND SerialNumber = '3960125290' ORDER BY ID; 

( -4 from your comment on another answer)

The best solution is to do this with cursors or application code, where you only need to make one pass and compare simple logic using variables in the cursor or application code.