Select sum from latest values in table

Question

SELECT SUM(CD.[Value]) AS [Value]
 FROM [{schema}].[CompanyData] CD
 INNER JOIN #Locations T
 ON T.LocationId = CD.CompanyId
 INNER JOIN (
 SELECT CDM.CompanyId, MAX(CDM.[Date]) as MaxDate
 FROM [{schema}].[CompanyData] CDM
 INNER JOIN #Locations T
 ON T.LocationId = CDM.CompanyId
 GROUP BY CDM.CompanyId
 ) TM 
 ON CD.CompanyId = TM.CompanyId AND CD.[Date] = TM.MaxDate

I have query to select SUM from latest values for each row with different CompanyId.

Is this double join of same tables (Query and subQuery) fine or I can improve somehow it?

Thanks.

Answer 1

The query you showed is not running as-is, where's this CFID alias coming from?

As SQL Server supports Standard SQL's Analytical Functions you might rewrite MAX/Subquery using RANK (or ROW_NUMBER):

SELECT ...
FROM
 ( SELECT CDM.CompanyId, 
 any additonal columns you need,
 RANK() OVER (PARTITION BY CDM.CompanyId
 ORDER BY CDM.[Date] DESC) AS rnk
 FROM [{schema}].[CompanyData] CDM
 INNER JOIN #Locations T
 ON T.LocationId = CDM.CompanyId
 ) TM
WHERE rnk = 1