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

I mean, I have 2 rows with the same SerialNumber.. one with the ID: 7331 And one with the ID: 7281. well the previous one is the the row with the ID: 7281. even if a row with an ID like 7330 exists but has a different SerialNumber it doesn't count as the previous one — Guy Messika, Sep 14 '13 at 11:39

John Woo · Accepted Answer · 2013-09-14T11:47:24.770

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.

Jeremy Smyth · Answer 2 · 2013-09-14T12:40:47.627

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.

edited Sep 14 '13 at 12:40

answered Sep 14 '13 at 11:50

Jeremy Smyth

23,270
2
52
65

in other rdbms, there are functions that can help you compare row by row, eg. `LAG and LEAD`. – John Woo Sep 14 '13 at 11:51
I tried the above answer and it returns 1398 rows instead of just 4. – Guy Messika Sep 14 '13 at 12:08
This example works for only one `SerialNumber`. I've changed it to show that explicitly. You could wrap this as a subquery to show rows for each serial number iteratively. – Jeremy Smyth Sep 14 '13 at 12:40

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

2 Answers2

Linked