Before even going to the solution you have to make sure that you have to have a sorting (order by) criteria to get consistency in getting first or last row. Not going to give you a complete SQL statement but to solve this problem you can use RANK.

lazyketa · Nov 29, 2018 7:35 AM

See below: Last edited: 29-Nov-18 11:29 AM

pidiiit · Nov 29, 2018 11:08 AM

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 Let me know if you have any concerns. Last edited: 29-Nov-18 11:29 AM

pidiiit · Nov 29, 2018 11:27 AM

This questions fits best on StackOverflow. I am surprised that somebody helped/answered the question in this site.

everestial007 · Nov 29, 2018 5:52 PM

Thanks pidiiit for the skript. I got the below result but I need the only highlighted row. Join the table Product and Material on ProductID. However, join the last row with if Status for each row is Pass. If the Status of any or all row is Fail, then join with the row that has Fail at first.Can I have a query please? Last edited: 30-Nov-18 09:51 AM Last edited: 30-Nov-18 09:52 AM Last edited: 30-Nov-18 09:53 AM

phone · Nov 30, 2018 9:51 AM

Thanks pidiiit . I got the below result but I need the only highlighted row. Join the table Product and Material on ProductID. However, join the last row with if Status for each row is Pass. If the Status of any or all row is Fail, then join with the row that has Fail at first. Can I have a query please?

phone · Nov 30, 2018 9:56 AM

Add this And where M.MATERIALNAME=Mname3

Dev_ · Nov 30, 2018 8:14 PM

Thanks Dev_, My situation is I don't know the value of both tables and there could be thousands of records in both tables. The above table is just for example. Join the table Product and Material on ProductID. However, join the last row with if Status for each row is Pass. If the Status of any or all row is Fail, then join with the row that has Fail at first. Can I have a query please?

phone · Dec 2, 2018 11:56 AM

check your inbox msg. I sent you the query

pidiiit · Dec 2, 2018 11:59 AM

Thanks pidiiit. I got the query and it worked.

phone · Dec 2, 2018 12:18 PM