Get 2 Most Recent Records By Product
The below query works like this:


It first uses the ROW_NUMBER window function to partition it by PRODUCTID and ORDER them by CREATEDATE from 1 to n
For STATUS = 'pass' you give 1 (0 otherwise) 
You then only keep the first 2 values with n &lt;= 2 (-> 1 or 2)
Finally if the sum of the 2 rows for a given PRODUCTID is equal to 2 and the count of distinct LEVELs is 2, it returns TRUE.


Query:

SELECT PRODUCTID
    , CASE WHEN SUM(STATUS) = 2 AND COUNT(DISTINCT LEVEL) = 2
          THEN 'TRUE'
          ELSE 'FALSE'
      END
FROM (
    SELECT PRODUCTID, LEVEL
        , STATUS = CASE WHEN STATUS = 'pass' THEN 1 ELSE 0 END
        , n = ROW_NUMBER() OVER(PARTITION BY PRODUCTID ORDER BY CREATEDATE DESC)
    FROM data
) agg
WHERE n &lt;= 2
GROUP BY PRODUCTID


See SQL Fiddle.

Output:

PRODUCTID   RUNSTATUS
ABC         TRUE
DEF         FALSE
GHI         FALSE
JKL         FALSE