CODEWITHSUNDEEP
mysqlsql
AdrianES
AdrianES

Reputation: 670

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 with all the times in the past.

Thanks in advance

Upvotes: 3

Views: 4184

Answers (2)

1000111
1000111

Reputation: 13519

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

Upvotes: 8

Deniz Aktürk
Deniz Aktürk

Reputation: 362

maybe you can help as a code

SELECT id, IF((id NOT IN(1,3,5,6)), 1, 0) as rowStatus FROM table HAVING rowStatus = 1;

I tried to explain how to use the conditions here

Upvotes: 0

Related Questions