Link to original post: https://community.oracle.com/thread/4312816
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 (not the 11 March line).
Thanks @"Frank Kulash" for your reply under my original post. The suggested solution is picking the 1st instance of a duplicated transaction reference (earliest date). I would the last instance of the date to be selected. The solution needs to be able to deal with situations where there might be any duplicate trade references, or two or more duplicate trade references.
Thanks