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

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

It's possible using joins. I used events as database name example.

SELECT x.* 
  FROM events x
  JOIN
     (
       SELECT MIN(c.id) id 
         FROM events a
         LEFT 
         JOIN events b 
           ON b.FinalState = a.FinalState 
          AND b.id = a.id - 1 
         LEFT 
         JOIN events c 
           ON c.FinalState = a.FinalState
          AND c.id >= a.id
         LEFT
         JOIN events d
           ON d.FinalState = a.FinalState
          AND d.id = c.id + 1
        WHERE b.id IS NULL 
          AND c.id IS NOT NULL
          AND d.id IS NULL
        GROUP 
           BY a.id
     ) y
    ON y.id = x.id; 

You could use the lag function to check if the state changes:

with cte as
(select CAST('2020-03-09 04:57:45.729' as datetime) as Event_timestamp,'Available' as Finalstate union
select '2020-03-09 05:14:59.659','Available' union
select '2020-03-09 09:29:53.165','Offline' union
select '2020-03-09 10:28:00.514','Available')

select x.Event_timestamp,x.Finalstate
from
(select *,lag(Finalstate) Over(Order by Event_timestamp) as lag_status
from cte ) x
where x.Finalstate<>coalesce(lag_status,'Z')

Here is a db-fiddle: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=cb59ec79e7be1363961971cea4308dff

Hope this helps.

This query solved my problem. All thanks to @GMB

SELECT
    Event_TimeStamp,
    FinalState
FROM (
    SELECT
        t.*,
        COALESCE(LAG(FinalState) over(ORDER BY Event_TimeStamp), 'offline')  lag_final_state,
        COALESCE(lead(FinalState) over(ORDER BY Event_TimeStamp), 'offline') lead_final_state
    FROM (
        SELECT
            Event_TimeStamp, 
            FinalState
        FROM AgentTraceData
        WHERE Event_TimeStamp BETWEEN '2019-11-17' AND '2020-03-10 23:59:59.999' AND username = 'xxxx' ORDER BY Event_TimeStamp
        ) t
    ) t
WHERE FinalState <> lag_final_state

Which gives the following result

+---------------------------+-------------+
|"Event_TimeStamp"          | "FinalState"|
+---------------------------+-------------| 
|"2019-11-18 02:01:16.395"  |"online"     |
|"2019-11-18 04:34:59.739"  |"offline"    |
|"2019-11-18 04:45:08.354"  |"online"     |
|"2019-11-18 07:30:13.909"  |"offline"    |
|"2019-11-18 08:00:20.647"  |"online"     |
|"2019-11-18 10:30:08.698"  |"offline"    |
+---------------------------+-------------+

I am going to try to explain as much as I can.

First we need to run the query with SELECT * to see all the columns appearing.

The output would look like this

+---------------------------+---------------+-------------------+------------------+
|"Event_TimeStamp"          |"FinalState"   |"lag_final_state"  |"lead_final_state"|
+---------------------------+---------------+-------------------+------------------+
|"2019-11-18 02:01:16.395"  |"online"       |"offline"          |"online"          |
|"2019-11-18 04:34:59.739"  |"offline"      |"online"           |"online"          |
|"2019-11-18 04:45:08.354"  |"online"       |"offline"          |"online"          |
+---------------------------+---------------+-------------------+------------------+

As per my requirement, I wanted to know the next FinalState value so final_state <> lead_final_state was not needed.

I added COALESCE() cause LEAD() and LAG() will give NULL values for rows which do not exists above or beyond the first SELECT query.