Select row where column is different from previous row

I am trying to write a query to track the changes of a boolean column in a table. The table looks like this

ClientHistory
-------------------------------------------------------------
| id | client_id | client_name | is_active    |   Timestamp |
-------------------------------------------------------------
| 1 |    1       |Example Client|    True     |   06/15/2020|
-------------------------------------------------------------
| 2 |    1       |Client Change |     True    |   06/16/2020|
-------------------------------------------------------------
| 3 |    1       |Client Change |   False     |  06/17/2020 |

So what i would want is row 3 where the is_active changed to false. Then after that i would want the next row where it changed to true again.

This is what i tried:

        SELECT a.*
        FROM client_history AS a
        WHERE a.is_active <>
            ( SELECT b.is_active
                FROM client_history AS b
                WHERE a.client_id = b.client_id
                AND a.timestamp > b.timestamp
                ORDER BY b.timestamp DESC
                LIMIT 1
            )

So the subquery is trying to get the previous row of the same client_id by getting the most recent timestamp before it . Then in query check if is_active does not equal the is_active from the previous row. But this is not working as planned. I expect as I trigger acttive/inactive it should be alternating in this query but it is not. Anybody got any tips?

Answer

Use window functions!

select ch.*
from (select ch.*,
             lag(is_active) over (partition by client_id order by timestamp) as prev_is_active
      from client_history ch
     ) ch
where is_active <> prev_is_active;

Advertisement

Answer