Alex Rivera | Logout

SQL: selecting rows where column value changed from previous row

Asked 2011-07-02T22:44:41.613
46

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

mysql sql
Edit
Report

2 Answers

12

Use rownum

I've got 0.05 seconds on 20000 rows

select a1.*
  from (select rownum R_NUM, TIMESTAMP, System, StatusA from TableX) a1 
  join (select rownum R_NUM, TIMESTAMP, SYSTEM, STATUSA from TABLEX) a2 
    on a1.R_NUM = a2.R_NUM+1 
 where a1.system = a2.system 
   and a1.StatusA != a2.StatusA
answered 2012-06-01T15:12:15.330
2

Here's a slightly shorter version with similar logic. I've tested this so often I'm sure it's efficient; primarily because it eliminates the correlated subquery (WHERE NOT EXISIS).

"c" is in there to make sure that b is directly below a - it says c (between them) can't be found (via the NULL test).

SELECT a.Timestamp, a.System, a.StatusA, a.StatusB
FROM tableX AS a
JOIN tableX AS b
    ON a.System = b.System
    AND a.Timestamp > b.Timestamp
LEFT JOIN tableX AS c
    ON a.System = b.System
    AND a.Timestamp > c.Timestamp
    AND b.Timestamp < c.Timestamp
WHERE c.System IS NULL
    AND a.StatusA <> b.StatusA;
answered 2011-07-03T08:30:10.987

Your Answer