Compare Past Records to Most Recent Record
One way would be using CROSS APPLY.

If you identify the most recent record for each product in the first table and then pass the criteria into the cross apply.

SELECT 
      ProductId
   ,  MAX(CASE WHEN t1.RunFailure = t2.RunFailure THEN 1 ELSE 0 END) AS IsMostCurrentRunSameLevelAsLastFailedRun
   FROM &lt;SomeTable&gt; t1
   CROSS APPLY (
      SELECT TOP 1
         RunFailure
         FROM &lt;SomeTable&gt; st
         WHERE st.ProductId = t1.ProductId
         AND   st.LevelCode = t1.LevelCode
         AND   st.SampleDate &lt; t1.SampleDate
         ORDER BY st.SampleDate) AS t2
   GROUP BY t1.ProductId


Alternatively, look into the windowing functions. You can do a great deal with them that may be more efficient.