mysql detect field value change

Question

I have found a very similar question with exact same title here: Mysql, Check field value change?, but it is not exactly what I need.

DB: MySql, Table: tbl_geodata

I have query:

SELECT id,timestamp,ver_fw FROM tbl_geodata
WHERE imei LIKE '353227026533507'

Result is:

--Full data
+------+---------------------+--------+
| id   | timestamp           | ver_fw |
+------+---------------------+--------+
                 ...
                 ...
| 813  | 2014-09-10 11:24:01 | 2.5.0  |       
| 1457 | 2014-09-15 14:07:03 | 2.5.0  |
| 1458 | 2014-09-15 14:15:33 | 2.5.0  |
| 1467 | 2014-09-15 15:08:29 | 2.5.0  |
| 1468 | 2014-09-15 15:19:36 | 2.5.0  |
| 1469 | 2014-09-15 16:35:46 | 2.5.2  |
| 1470 | 2014-09-15 16:52:58 | 2.5.2  |
| 1471 | 2014-09-15 17:14:47 | 2.5.2  |
| 1472 | 2014-09-15 17:52:59 | 2.5.3  |
| 1473 | 2014-09-16 09:51:59 | 2.5.3  |
| 1474 | 2014-09-16 09:53:16 | 2.5.3  |
| 1506 | 2014-09-16 10:36:45 | 2.5.3  |
                  ...
                  ...

What I want is to check any changes in ver_fw and select only this rows:

--Correct results
+------+---------------------+--------+
| id   | timestamp           | ver_fw |
+------+---------------------+--------+
| 4    | 2014-07-08 18:02:22 | 2.4.19 | 
| 813  | 2014-09-10 11:24:01 | 2.5.0  | 
| 1469 | 2014-09-15 16:35:46 | 2.5.2  | 
| 1472 | 2014-09-15 17:52:59 | 2.5.3  | 
| 1543 | 2014-09-16 15:28:18 | 2.5.4  | 
| 1551 | 2014-09-17 11:37:12 | 2.4.19 |     
| 1555 | 2014-09-18 12:11:04 | 2.5.0  | 
| 1557 | 2014-09-18 13:59:22 | 2.5.4  | 
| 1563 | 2014-09-18 14:43:22 | 2.5.5  | 
| 1637 | 2014-09-23 15:42:07 | 2.5.6  | 
| 1660 | 2014-09-24 10:21:42 | 2.5.7  | 
| 1682 | 2014-09-25 14:51:20 | 2.5.8  | 
| 1692 | 2014-09-26 10:38:39 | 2.4.19 |     
| 5290 | 2015-04-08 20:11:38 | 2.4.81 |
+------+---------------------+--------+

I have tried to change my query like answered in the same question, but I've failed:

SELECT 
    id,
    TIMESTAMP,
    @prev_ver := ver_fw ver_fw
FROM tbl_geodata, (SELECT @prev_ver := -1)s
WHERE imei LIKE  '353227026533507' AND @prev_ver != tbl_geodata.ver_fw

Result:

--Incorrect results
+------+---------------------+--------+
| id   | TIMESTAMP           | ver_fw |
+------+---------------------+--------+
|    4 | 2014-07-08 18:02:22 | 2.4.19 |
|  813 | 2014-09-10 11:24:01 | 2.5.0  |
| 1551 | 2014-09-17 11:37:12 | 2.4.19 |
| 1555 | 2014-09-18 12:11:04 | 2.5.0  |
| 1692 | 2014-09-26 10:38:39 | 2.4.19 |
+------+---------------------+--------+

Unfortunately, I can not understand a reason. How can I get only rows with value that changes from previous?

P.S. sorry for my english.

Answer 1

Here's a way to do it using variables:

SELECT id, `timestamp`, ver_fw
FROM (
  SELECT id, `timestamp`, ver_fw,
         IF ( @prev_ver <> ver_fw, 
             IF (@prev_ver := ver_fw, 1, 1),
             IF (@prev_ver := ver_fw, 0, 0)) AS IsDifferent
  FROM tbl_geodata
  CROSS JOIN (SELECT @prev_ver := '-1') AS var
  WHERE imei LIKE '353227026533507'
  ORDER BY `timestamp` ) t
WHERE IsDifferent = 1
ORDER BY `timestamp`

The first-level IF checks for inequality. The second level is used in order to assign @prev_ver its next value and to return 1 / 0, depending on inequality / equality to previous value of ver_fw.

Demo here

Answer 2

Getting the previous value using variables is a bit more challenging than it should be.

Fortunately, if you have an index on tbl_geodata(timestamp) (or id if that defines the ordering), you can get good performance with a correlated subquery:

select gd.*,
       (select gd2.ver_fw
        from tbl_geodata gd2
        where gd2.timestamp < gd.timestamp <-- id might also be used here
        order by gd2.timestamp desc
        limit 1
       ) as prev_ver_fw
from tbl_geodata gd
having prev_ver_fw is null or prev_ver_fw <> ver_fw;

Note that this uses a MySQL extension by using a having clause with no group by. This saves an extra layer of subquery'ing for the final condition.