CREATE TABLE #TestData

(

Product NVARCHAR(100),

Parts NVARCHAR(100),

Status NVARCHAR(100)

)

INSERT INTO #TestData(Product,Parts,Status)

SELECT 'Laptop1','mouse','OK' UNION ALL

SELECT 'Laptop1','screen','OK' UNION ALL

SELECT 'Laptop1','button','OK' UNION ALL

SELECT 'Laptop2','mouse','OK' UNION ALL

SELECT 'Laptop2','screen','OK' UNION ALL

SELECT 'Laptop2','button','NOT OK'

This can be achieved using RANKING FUNCTION

WITH C AS

(

SELECT *,ROW_NUMBER() OVER(PARTITION BY Product ORDER BY Product) AS Rn FROM #TestData

),

C1 AS

(

SELECT *, MAX(Rn) OVER(PARTITION BY Product)AS MaxRn FROM C

)

SELECT Product,Status FROM C1 WHERE Rn=MaxRn