Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2009
    Posts
    58

    Unanswered: problem with join want highest value record from multiple records

    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.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Forget about the join for a moment and try to select only from table2 only those records whose amend_date is the latest for each part number.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •