mysql - SQL: selecting rows where column value changed from previous row

Question

Let's say I have this (MySQL) database, sorted by increasing timestamp:

Timestamp   System StatusA StatusB 
2011-01-01     A      Ok     Ok      
2011-01-02     B      Ok     Ok     
2011-01-03     A     Fail   Fail     
2011-01-04     B      Ok    Fail     
2011-01-05     A     Fail    Ok      
2011-01-06     A      Ok     Ok      
2011-01-07     B     Fail   Fail    

How do I select the rows where StatusA changed from the previous row for that system? StatusB doesn't matter (I show it in this question only to illustrate that there may be many consecutive rows for each system where StatusA doesn't change). In the example above, the query should return the rows 2011-01-03 (StatusA changed between 2011-01-01 and 2011-01-03 for SystemA), 2011-01-06, 2011-01-07.

The query should execute quickly with the table having tens of thousands of records.

Thanks

Answer 1

SELECT a.*
FROM tableX AS a
WHERE a.StatusA <>
      ( SELECT b.StatusA
        FROM tableX AS b
        WHERE a.System = b.System
          AND a.Timestamp > b.Timestamp
        ORDER BY b.Timestamp DESC
        LIMIT 1
      ) 

But you can try this as well (with an index on (System,Timestamp):

SELECT System, Timestamp, StatusA, StatusB
FROM
  ( SELECT (@statusPre <> statusA AND @systemPre=System) AS statusChanged
         , System, Timestamp, StatusA, StatusB
         , @statusPre := StatusA
         , @systemPre := System
    FROM tableX
       , (SELECT @statusPre:=NULL, @systemPre:=NULL) AS d
    ORDER BY System
           , Timestamp
  ) AS good
WHERE statusChanged ;