Gaps and Islands Getting Last Island Window

I have a table with status changes that I want to chunk into islands and eventually get my most current island based on when my status changed. I'm using this query to retrieve the current, previous and next status codes from my table:

select 
    *, 
    case 
        when ping.GpsStatusTypeId <> ping.previousStatus 
            then 1 
            else 0 
    end as islandStarted
from
    (select 
         Id,
         GpsStatusTypeId,
         CreatedAt,
         lag(GpsStatusTypeId, 1) over (order by CreatedAt) as previousStatus,
         lead(GpsStatusTypeId, 1) over (order by CreatedAt) as nextStatus
     from 
         dbo.GpsPing p) ping

The result looks like this:

I'm looking to pull out only my last island (GpsStatusTypeId = 21) from the result.

You need a running COUNT to count up how many islands you passed. This becomes the group ID. Then just filter for the first group ID.

In this case it's probably better to go descending, and flip the case to =

with WithLag as (
    select
      Id,
      GpsStatusTypeId,
      CreatedAt,
      lag(GpsStatusTypeId) over (order by CreatedAt DESC) as previousStatusDesc
    from dbo.GpsPing p
),
WithCount as (
    select *,
      case when ping.GpsStatusTypeId = ping.previousStatus then null else 1 end as islandStarted,
      count(case when ping.GpsStatusTypeId = ping.previousStatus then null else 1 end)
           over (order by CreatedAt DESC) as groupId
    from WithLag
)
select *
from WithCount
where groupId = 0;