Compare value from previous record
I assumed that "failed" means passed = 0.

There are some detail missing from the question, i.e. do we need to compare the very last row (when ordered by sampledate) with the previously last "failed" row? If yes, then the following query will do:

with last_row as
( select top (1) sampledate, level
  from dbo.tablex
  -- where failed = 0 
  order by sampledate desc
)
select top (1) result =
    case when t.level = r.level 
        then 1 else 0
    end 
from dbo.tablex as t
  join last_row as r 
  on t.sampledate &lt; r.sampledate
where t.passed = 0
order by t.sampledate desc ;




If, on the other side, we want to compare the last "failed" row with the previous "failed" row, then we could uncomment the where failed = 0 line above or use the less complicated, just find the last 2 failed rows and compare their levels with a simple group by:

with last_2_failed_rows as
( select top (2) sampledate, level
  from dbo.tablex
  where failed = 0 
  order by sampledate desc
)
select result =
    case when min(level) = max(level) 
        then 1 else 0
    end 
from last_2_failed_rows ;