Select a record just if the one before it has a lower value

Question

I work with mysql and I've never encountered such a great challenge. I 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 the table where Remain_Toner_Black is higher than Remain_Toner_Black of the previous row in the table with the same SerialNumber (by previous I mean lower ID with the same SerialNumber).

For the above records I want the results below:

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

Answer 1

MySQL (or indeed most other RDBMS) doesn't easily allow cross-row comparisons.

To do this sort of query, you must either perform a very expensive join comparing one version of the table to several rows of another version of the table, or construct a complex expression using user 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;

(the -4 is from your comment to another answer)

A better solution is to do this with cursors or in your application code, where you need only do one pass and compare simple logic by using variables in the cursor or application code.

Answer 2

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 ║
╚══════╩═════════════════╩═════════════╩════════════╝

BRIEF EXPLANATION

What the query above does is it generates a sequential number which mimics ROW_NUMBER() on other RDBS for every SerialNumber ordered by ID in ascending order.

The two subquery is then joined via the SerialNumber and sequential number generated. On the generated number, the value on the first subquery must be equal to plus one of the value on the second subquery to get the number of toner on the next reord.