mysql - How to get select rows that have first occurrences before column value changes in MYSQL8

Question

I have a MYSQL8 table with Event_TimeStamp and FinalStateand it looks like this

+---------------------------+---------------+
|"Event_TimeStamp"          |"FinalState"   |
+---------------------------+---------------+
|"2020-03-09 04:57:45.729"  |"Available"    |
|"2020-03-09 05:14:59.659"  |"Available"    |
|"2020-03-09 05:27:56.341"  |"Available"    |
|"2020-03-09 05:41:01.554"  |"Available"    |
|"2020-03-09 05:58:07.803"  |"Available"    |
|"2020-03-09 06:06:09.745"  |"Available"    |
|"2020-03-09 06:18:07.663"  |"Available"    |
|"2020-03-09 06:26:24.273"  |"Available"    |
|"2020-03-09 09:29:53.165"  |"Offline"      |
|"2020-03-09 10:28:00.514"  |"Available"    |
|"2020-03-09 12:47:54.130"  |"Available"    |
|"2020-03-09 13:01:30.117"  |"Available"    |
|"2020-03-09 13:01:59.774"  |"Offline"      |
|"2020-03-09 13:19:15.772"  |"Available"    |
|"2020-03-09 14:19:51.521"  |"Available"    |
|"2020-03-09 14:50:16.872"  |"Offline"      |
+---------------------------+---------------+

I have to extract rows from the above such that it will have the rows with first "Available" and "Offline", so the output would look like this

+---------------------------+---------------+
|"Event_TimeStamp"          |"FinalState"   |
+---------------------------+---------------+
|"2020-03-09 04:57:45.729"  |"Available"    |
|"2020-03-09 09:29:53.165"  |"Offline"      |
|"2020-03-09 10:28:00.514"  |"Available"    |
|"2020-03-09 13:01:59.774"  |"Offline"      |
|"2020-03-09 13:19:15.772"  |"Available"    |
|"2020-03-09 14:50:16.872"  |"Offline"      |
+---------------------------+---------------+

I tried a few ways with GROUP BY but I get only the first entries for each of the FinalState and not the rest of them.

Is there a way to get this done with an QUERY or should I write it out in PHP?

标签： mysqlsqlwindow-functionsgaps-and-islandsmysql-8.0

Answer 1

You can use lag() and lead() to exhibit records whose final_state is different that in the previous or next row:

select
    event_timestamp,
    final_state
from (
    select 
        t.*, 
        lag(final_state) over(order by event_timestamp)  lag_final_state,
        lead(final_state) over(order by event_timestamp) lead_final_state
    from mytable t
) t
where final_state <> lag_final_state or final_state <> lead_final_state