Unanswered: problem with join want highest value record from multiple records
I am facing problem in my join conditions in query. I Have two table which I want to join. Problem is for part number in table1 there are multiple values in table2 and I want to put logic on join condition which will pick the record which has max amend_date (this is one of the field of the table) in table2 for that part number.
Here is join condition in which I want to put the logic so that records with max amend date will come only.
LEFT OUTER JOIN FCFM.PO PAA ON (TRIM(PAA.I_PO_PROD_GRP)= TRIM(PO.I_PO_PROD_GRP) and PAA.I_MOD_YR = PO.I_MOD_YR and PAA.I_PART=PO.I_PART )
left JOIN FCFM.PO_AMEND PA ON ( PA.I_MOD_YR = PAA.I_MOD_YR and PA.I_PART=PAA.I_PART
here i want to put that condition to get highest date records only.
i tried using self join,