CODEWITHSUNDEEP
sqlsql-server
PJW
PJW

Reputation: 5417

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.

Upvotes: 0

Views: 34

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269683

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;

Upvotes: 2

Related Questions