CODEWITHSUNDEEP
mysqlsqlselectjoin
Andrew Shulgin
Andrew Shulgin

Reputation: 39

MySQL SELECT following row with a different column value

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.

Upvotes: 3

Views: 109

Answers (2)

StanislavL
StanislavL

Reputation: 57381

You can use @rowFlag variable. Each row check whether it's equals to flag. If yes 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;

SQL Fiddle

Upvotes: 2

Fahad Anjum
Fahad Anjum

Reputation: 1256

You can check this, as this contains old value and new values as well. Change 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 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

Upvotes: 0

Related Questions