How do you grab the max of a date in SQL?

Question

I wrote this query:

SELECT MAX(effectivedate) as MaxEffectiveDate, AccountId, RTRIM(codeStatus.Description) as Status 
FROM PlcPolicy plc
JOIN dbo.CodePlcStatus codeStatus WITH (NOLOCK) ON codeStatus.StatusId = plc.StatusId  

WHERE AccountName LIKE 'Hertz%' 
GROUP BY AccountId, RTRIM(codeStatus.Description)

I want to return the the record with the highest effective date for each accountid.

Here the results from the query. As you can see it doesn't pull the highest date. It should only give me one row with the effective date '2021-07-01' if I'm pulling MAX but it still gives me 2??

Answer 1

You will need to join the table to itself. To return only the record with the highest effective date for each AccountId, you can use a subquery or a CTE (Common Table Expression). Here's an example using a subquery:

SELECT plc.MaxEffectiveDate, plc.AccountId, RTRIM(codeStatus.Description) AS Status
    FROM (
      SELECT AccountId, MAX(effectivedate) AS MaxEffectiveDate
      FROM PlcPolicy
      WHERE AccountName LIKE 'Hertz%'
      GROUP BY AccountId
    ) AS maxDates
    JOIN PlcPolicy plc ON plc.AccountId = maxDates.AccountId AND plc.effectivedate = maxDates.MaxEffectiveDate
    JOIN dbo.CodePlcStatus codeStatus WITH (NOLOCK) ON codeStatus.StatusId = plc.StatusId;