MySQL SELECT next row with different column value
I have a report card:
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
The strings may be in the wrong order (for example, b1
and b0
), so they must be sorted by first ts
.
I am trying to make an efficient query for sn
to get the rows where the current and the next are flag
different.
As a result, I would like 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 is not a problem to create additional columns or tables.
source to share
You can use a variable @rowFlag
. Each line checks if it is equal to a value. If so, set the filter field to 0 to skip it later.
select old_ts, ts, old_data, data, new_flag as flag
from
(select
t.ts,
t.data,
case when @rowFlag=flag then 0 else 1 end as filter,
flag as new_flag,
@rowFlag:=flag as old_flag,
@old as old_data,
@old:=data,
@old_ts as old_ts,
@old_ts:=ts
from test t, (select @rowFlag:=-1, @old:=null, @old_ts:=null) as def
where sn='a'
order by ts) as sub
where filter=1 and old_ts is not null;
source to share
You can check this as it contains the old value and the new values. Modify new_table with your actual table name.
select a.sn, a.ts as oldts, b.ts as newts,
a.data as old_data, b.data as data, a.flag as old_flag , b.flag as flag
from (
select sn, ts, data, flag ,
if(@oldSn = sn,@rowNumber := @rowNumber + 1,@rowNumber := 1) as row_number,
@oldSn := sn as curentsn
from new_table
order by sn, ts ) a
join (
select sn, ts, data, flag ,
if(@oldSn1 = sn,@rowNumber := @rowNumber + 1,@rowNumber := 1) as row_number,
@oldSn1 := sn as curentsn
from new_table
order by sn, ts ) b on a.sn = b.sn
and a.row_number + 1 = b.row_number
where a.flag != b.flag
Output of the above query
sn, oldts, newts, old_data, data, old_flag, flag a, 1494825611, 1494825613, a0, a1, 1, 0 a, 1494825617, 1494825623, a2, a3, 0, 1 a, 1494825645, 1494825648, a5, a6, 1, 0 b, 1494825643, 1494825644, b0, b1, 0, 1
source to share