I need a help to get data from a table which has id,serialnumber,process(prcs) and result The query able to get the recent failed process(prcs) of that serialnumber,if the recent process(prcs) is Pass, it will not show. But my problem is if the Last process is PASS,do not show the serial number.Now im able to get the recent Failed process but i want to ignore it if the last process is pass.Thank you

The Query i tried-:

SELECT tt.id,tt.serialnumber,tt.prcs,tt.result
FROM tblTest tt
INNER JOIN
    (SELECT serialnumber,prcs MAX(id) AS MaxID
    FROM tblTest 
    GROUP BY  serialnumber,prcs) groupedtt 
ON tt.prcs = groupedtt.prcs AND tt.id = groupedtt.MaxID
WHERE result='FAIL' AND tt.sn='A01'  
ORDER by serialnumber
Try:

WITH cte AS ( SELECT id, 
                     serialnumber, 
                     prcs, 
                     result, 
                     ROW_NUMBER() OVER (PARTITION BY serialnumber 
                                        ORDER BY id DESC) rn
              FROM tblTest )
SELECT id, serialnumber, prcs, result
FROM cte
WHERE rn = 1
  AND result = 'FAIL'



  if lastprocess and 5th process fail, should show both


Try:

SELECT t1.*
FROM tblTest t1
WHERE t1.result = 'FAIL'
  AND NOT EXISTS ( SELECT 1
                   FROM tblTest t2
                   WHERE t1.serialnumber = t2.serialnumber 
                     AND t1.id &lt; t2.id
                     AND t2.result != 'FAIL' )