AdrianES AdrianES - 1 month ago 6
SQL Question

Mysql select row when column value changed

I need help or idea how to write it, a mysql statement. I need to select a row when a specific column changes its value. Rows after that with the same value should not be selected. For example we have a table like this, populate with this values:

ID status_1 status_2 status_3 timestamp
1 0 0 0 2016-01-01 00:00:00
2 0 0 1 2016-01-01 01:00:00
3 0 1 0 2016-01-01 02:00:00
4 0 1 1 2016-01-01 03:00:00
5 1 0 0 2016-01-01 04:00:00
6 1 0 1 2016-01-01 05:00:00
7 1 1 0 2016-01-01 06:00:00
8 1 1 1 2016-01-01 07:00:00


If i wanna select the rows when the status_1 change, the query should select the rows with ID 1 AND 5, if i am working with status_2 rows with IDs: 1, 3, 5, 7, and if i am working with status_3 all IDs. Hope someone can help me like all the times in the past.

Thanks in advance

Answer

MySQL user defined variables would help you in this case.

Every time you see a new status assign 1 as row number to the corresponding row.

And if you see the same status that you saw in the previous row then assign an incremented row number instead.

This way you can finally filter the records having row number = 1 only. These particular records are actually showing difference comparing to their immediate previous row

SELECT 
*
FROM 
(
 SELECT 
  *,
  IF(@prevStatus = YT.status_1, @rn := @rn + 1,
    IF(@prevStatus := YT.status_1, @rn := 1, @rn := 1)
  ) AS rn
 FROM your_table YT
 CROSS JOIN 
 (
  SELECT @prevStatus := -1, @rn := 1
 ) AS var 
 ORDER BY YT.ID
) AS t
WHERE t.rn = 1
ORDER BY t.ID
Comments