Oracle SQL - filter output for max date via SQL

[G_Whit-UK]

Hi,

I'm attempting to compile a long Oracle SQL where one fo 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.  I know I can do this in Power Query, but I need the solution to be SQL based.

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:

Example output

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

[G_Whit-UK · Accepted solution]

To those who might be interested in knowing the solution:  Solution posted on the Oracle SQL & PL/SQL Community blog, Thread ID 4312816, title "Oracle SQL - filter ouput for max date via SQL".

[edhans]

You might want to consult an Oracle form as this form is for Power Query and Power BI. Someone that is deeply knowledgable about composing native SQL statements in Oracle may stop by but an Oracle forum would be faster.

That said, not sure what the SQL you provided is doing, but if you can do it in Power Query against the Oracle server, Power Query can fold many query statements. Do it in Power Query then right-click on the last step and see if "View Native Query" is showing. If it is, that is your SQL statement.

Not all transformations Power Query does though are folded, so it might be greyed out.