SQL: selecting rows where column value changed from previous row

user_68936 0
Viewed 95000

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
7 Answers
SELECT   a.*
FROM    (select row_number() over (partition by System order by Timestamp asc) as aRow, Timestamp, System, StatusA, StatusB from tableX) as a
left join (select row_number() over (partition by System order by Timestamp asc) as bRow, Timestamp, System, StatusA, StatusB from tableX) as b on a.aRow = b.bRow + 1 and a.System = b.System 
where (a.StatusA != b.StatusA or b.StatusA is null)

It will return first row and rows where value is different.

user_77033980

This is exactly what windowing functions as built for. LAG will give the exact answer:

create table t1 (ts date, sys char(1),stata varchar(10),statb varchar(10));

insert into t1 values
('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');

select * from (
select ts,sys,stata,lag(stata) over(partition by sys order by ts asc) as prev from t1
) as subsel where stata!=prev
user_56078280
Related