SQL Joining One Table to a Selection of Rows from Second Table that Contains a Max Value per Group

I have a table of Cases with info like the following –

ID	CaseName	Date	Occupation
11	John	2020-01-01	Joiner
12	Mark	2019-10-10	Mechanic

And a table of Financial information like the following –

ID	CaseID	Date	Value
1	11	2020-01-01	1,000
2	11	2020-02-03	2,000
3	12	2019-10-10	3,000
4	12	2019-12-25	4,000

What I need to produce is a list of Cases including details of the most recent Financial value, for example –

ID	CaseName	Occupation	Lastest Value
11	John	Joiner	2,000
12	Mark	Mechanic	4,000

Now I can join my tables easy enough with –

SELECT *
FROM Cases AS c
LEFT JOIN Financial AS f ON f.CaseID = c.ID

And I can find the most recent date per case from the financial table with –

SELECT CaseID, MAX(Date) AS LastDate
FROM Financial 
GROUP BY CaseID

But I am struggling to find a way to bring these two together to produce the required results as per the table set out above.

Answer

A simple method is window functions:

SELECT *
FROM Cases c LEFT JOIN
     (SELECT f.*, MAX(date) OVER (PARTITION BY CaseId) as max_date
      FROM Financial f
     ) f
     ON f.CaseID = c.ID AND f.max_date = f.date;