Hi,
I'm attempting to compile a long Oracle SQL where one of the sub SQLs is returning multiple lines with the same transaction reference. I would like to amend the SQL so that only the lines with the latest date (max date) for each transaction is reflected in the output.
The subset of the current SQL is as follows:
SELECT distinct (LOAN_MSTR.I_LOAN_NUM), LOAN_STAT.C_LOAN_STAT, LOAN_STAT.D_END
FROM LOAN_MSTR
INNER JOIN AUD_LOAN on LOAN_MSTR.I_LOAN_ID = AUD_LOAN.I_LOAN_ID
INNER JOIN DPT on AUD_LOAN.I_DPT_ID = DPT.I_DPT_ID
INNER JOIN LOAN_HIST on LOAN_MSTR.I_LOAN_NUM = LOAN_HIST.I_LOAN_NUM
INNER JOIN LOAN_STAT on LOAN_HIST.I_LOAN_OMNI_ID = LOAN_STAT.I_LOAN_OMNI_ID
WHERE (AUD_LOAN.D_CAL >= TO_DATE ('01/11/2019', 'DD/MM/YYYY') and AUD_LOAN.D_CAL <= TO_DATE ('05/11/2019', 'DD/MM/YYYY') and DPT.C_RGN = 'US' and LOAN_STAT.D_END <= TO_DATE ('05/11/2019', 'DD/MM/YYYY'))
Order by LOAN_MSTR.I_LOAN_NUM
The output for the above is as follows:
Using the results in the red box from the above example, I would like to only see the entry for 11 April for that particular transaction reference.
Thanks