SQL Server : return most recent record based on date in column

Question

I'm having an issue with a pretty basic query, my temp table has a primary key and a column named PropertyID1.

I'm joining to another table that has 3 columns:

PropertyID, SalePrice, SaleDate

The query joins on PropertyID1 and PropertyID, what I'm trying to get is the most recent iteration of the matching PropertyID as there can be 10+ matches per ID and I need the most recent only, with the column SaleDate being used to select the required records.

So the initial query is

CREATE TABLE ##RPP_CHECK
(
     ID INT IDENTITY(1,1) PRIMARY KEY,
     PropertyID1 VARCHAR(255) NULL
);

INSERT INTO ##IDCHECK
VALUES (41572498), (41484495), (41590235), (41611406)

SELECT 
    ID, ##IDCHECK.PropertyID1, PropertyID, SalePrice, SaleDate 
FROM 
    ##IDCHECK
LEFT JOIN 
    [ODS].[RS1] ON [ODS].[RS1].[PropertyID] = ##IDCHECK.PropertyID1 
ORDER BY 
    ID

Which returns

ID  PropertyID1 PropertyID  SalePrice   SaleDate
--------------------------------------------------
1   41572498    41572498    0.0      01-01-2011
1   41572498    41572498    0.0      01-01-2012
1   41572498    41572498    1000     01-01-2018
2   41484495    41484495    1200     01-02-2018
3   41590235    41590235    2000     01-03-2018
3   41590235    41590235    0.0      01-01-1999
4   41611406    41611406    5000     01-10-2018

What I need it to return is

ID  PropertyID1 PropertyID  SalePrice   SaleDate
------------------------------------------------
1   41572498    41572498    1000    01-01-2018
2   41484495    41484495    1200    01-02-2018
3   41590235    41590235    2000    01-03-2018
4   41611406    41611406    5000    01-10-2018

I tried looking at some of the answers posted here, but they don't quite seem to fit, I'm pretty sure this is either a MAX( issue, or grouping, but not 100% sure so happy for any tips you can offer.

Cheers

Answer 1

APPLY often has the best performance:

SELECT ic.*, r.*
FROM ##IDCHECK ic OUTER APPLY
     (SELECT TOP (1) r.*
      FROM [ODS].[RS1] r
      WHERE r.[PropertyID] = ic.PropertyID1     
      ORDER BY r.SaleDate DESC
     ) r;