Posted by: pidiiit November 29, 2018
Login in to Rate this Post:
0
?
Use this. Rownumber will assign each row a value and rank will assign 1 as fail 2 as pass.
SELECT P.PRODUCT_ID,
P.PRODUCT_NAME,
M.MATERIAL_ID,
M.PRODUCT_ID,
M.MATERIALNAME,
M.STATUS
FROM PRODUCT_TABLE P
JOIN
(
SELECT MATERIAL_ID,
PRODUCT_ID,
MATERIALNAME,
[STATUS],
RANK() OVER(PARTITION BY PRODUCT_ID ORDER BY [STATUS]) AS STATUS_COLUMN
FROM MATERIAL_TABLE
) M ON P.PRODUCT_ID = M.PRODUCT_ID
WHERE M.STATUS_COLUMN = 1
SELECT P.PRODUCT_ID,
P.PRODUCT_NAME,
M.MATERIAL_ID,
M.PRODUCT_ID,
M.MATERIALNAME,
M.STATUS
FROM PRODUCT_TABLE P
JOIN
(
SELECT MATERIAL_ID,
PRODUCT_ID,
MATERIALNAME,
[STATUS],
RANK() OVER(PARTITION BY PRODUCT_ID ORDER BY [STATUS]) AS STATUS_COLUMN
FROM MATERIAL_TABLE
) M ON P.PRODUCT_ID = M.PRODUCT_ID
WHERE M.STATUS_COLUMN = 1
Let me know if you have any concerns.
Last edited: 29-Nov-18 11:29 AM