3

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

Ashish Bhavsar
  • 236
  • 4
  • 18
AdrianES
  • 670
  • 3
  • 13
  • 29
  • This sounds like a gaps and islands problem. – Tim Biegeleisen Oct 28 '16 at 10:22
  • In case your ids dont have gaps (e.g. there is no id missing/deleted) and they are in the order you think of as THE order, it is a simple left join, otherwise it is a little more complicated, so you should specify that. – Solarflare Oct 28 '16 at 10:24

2 Answers2

8

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
1000111
  • 13,169
  • 2
  • 28
  • 37
0

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

Deniz Aktürk
  • 362
  • 1
  • 9
  • I am 100% certain he doesn't know beforehand which rows are the ones he wants to get, otherwise he could just write `where id in (1,3,5,6)` (or `not in`) – Solarflare Oct 28 '16 at 10:29
  • I tried something similar with if condition and recursion but @1000111 already got an answer for my post. Thanks for trying. – AdrianES Oct 28 '16 at 10:34