Skip to content
Advertisement

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 –

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

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.

Advertisement

Answer

A simple method is window functions:

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement