I have a tabe like this:
sn(char) ts(int) data(char) flag(tinyint)
'a' 1494825611 'a0' 1
'a' 1494825613 'a1' 0
'a' 1494825617 'a2' 0
'a' 1494825623 'a3' 1
'a' 1494825634 'a4' 1
'b' 1494825644 'b1' 1
'b' 1494825643 'b0' 0
'a' 1494825645 'a5' 1
'a' 1494825648 'a6' 0
'b' 1494825658 'b2' 1
Rows may be in a wrong order (like b1 and b0), so they should be sorted by ts first.
I am trying to make an efficient query for sn to get rows where the current and the next flag differs.
As a result, I'd want something like this for sn 'a':
old_ts ts old_data data flag
1494825611 1494825613 'a0' 'a1' 0
1494825617 1494825623 'a2' 'a3' 1
1494825645 1494825648 'a5' 'a6' 0
and for sn 'b':
old_ts ts old_data data flag
1494825643 1494825644 'b0', 'b1' 1
It's not a problem to create additional columns or tables.