title: SQL - Returning recent payment date and amount
body: <p>I have created the following query in Oracle to return the most recent payment date and corresponding payment amount from a customer.  It works because I did not include the Amount field.  If I do, it results in multiple rows per company.
  Many thanks in advance.</p>
  
  <p><strong>WORKS (but does not include recent Amount)</strong></p>
  
  <pre>SELECT COMPANY_NAME, TERM_CODE, 
  MAX(LAST_PYMT) AS LAST_PYMT_REC, 
  AR_BALANCE, MAX_CREDIT_AR, OFFSET_BALANCE AS CREDITBAL_W_AP_OFFSETS 
  FROM 
  (
  SELECT COMPANY_NAME,  s2.LAST_PYMT, s2.AMOUNT, 
  CURRENT_BALANCE AS AR_BALANCE,
  (CURRENT_BALANCE - AP_TOTAL) AS OFFSET_BALANCE,
  TERM_CODES.TERM_CODE,
  AP_TOTAL,
  MAX_CREDIT AS MAX_CREDIT_AR
  FROM COMPANIES s1
  LEFT OUTER JOIN CTIRELAND.term_codes ON term_codes.tmc_auto_key = s1.tmc_auto_key
  
  inner join
  (
    select CR_DETAIL.ENTRY_DATE LAST_PYMT, MAX(CR_DETAIL.CRD_AUTO_KEY),
      CR_DETAIL.AMOUNT, CR_DETAIL.CMP_AUTO_KEY
    from CR_DETAIL
    GROUP BY CR_DETAIL.CRD_AUTO_KEY, CR_DETAIL.CMP_AUTO_KEY, CR_DETAIL.ENTRY_DATE, CR_DETAIL.AMOUNT
  ) s2
    on s1.CMP_AUTO_KEY = s2.CMP_AUTO_KEY 
  
    ORDER BY s1.company_name
    )
    GROUP BY COMPANY_NAME, AR_BALANCE, MAX_CREDIT_AR, OFFSET_BALANCE, TERM_CODE
    ORDER BY AR_BALANCE DESC
  </pre>
  
  <p><strong>DOES NOT WORK (returns every Amount made by each company not simply the most recent)</strong>
   <pre>SELECT COMPANY_NAME, TERM_CODE, 
  MAX(LAST_PYMT) AS LAST_PYMT_REC, <strong>AMOUNT</strong>, 
  AR_BALANCE, MAX_CREDIT_AR, OFFSET_BALANCE AS CREDITBAL_W_AP_OFFSETS 
  FROM 
  (
  SELECT COMPANY_NAME,  s2.LAST_PYMT, s2.AMOUNT, 
  CURRENT_BALANCE AS AR_BALANCE,
  (CURRENT_BALANCE - AP_TOTAL) AS OFFSET_BALANCE,
  TERM_CODES.TERM_CODE,
  AP_TOTAL,
  MAX_CREDIT AS MAX_CREDIT_AR
  FROM COMPANIES s1
  LEFT OUTER JOIN CTIRELAND.term_codes ON term_codes.tmc_auto_key = s1.tmc_auto_key</p>
  
  <p>inner join
  (
    select CR_DETAIL.ENTRY_DATE LAST_PYMT, MAX(CR_DETAIL.CRD_AUTO_KEY),
      CR_DETAIL.AMOUNT, CR_DETAIL.CMP_AUTO_KEY
    from CR_DETAIL
    GROUP BY CR_DETAIL.CRD_AUTO_KEY, CR_DETAIL.CMP_AUTO_KEY, CR_DETAIL.ENTRY_DATE, CR_DETAIL.AMOUNT
  ) s2
    on s1.CMP_AUTO_KEY = s2.CMP_AUTO_KEY </p>
  
  <p>ORDER BY s1.company_name
    )
    GROUP BY COMPANY_NAME, AR_BALANCE, MAX_CREDIT_AR, OFFSET_BALANCE, TERM_CODE, <strong>AMOUNT</strong>
    ORDER BY AR_BALANCE DESC
  </pre></p>