problem with join want highest value record from multiple records

mac4rfree · #1 (**permalink**) 08-10-10, 11:07

Hi All,
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.

Code:

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,
pa.D_PO_AMEND_ENTRD= max(pa.D_PO_AMEND_ENTRD)

but not working plz help.