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??

Well it *is* giving you the max date, but as you can clearly see you are grouping by Status in addition to AccountId and you have *two* unique values, hence you have 2 groups. Remove Status from the query. — Stu, Jun 06 '23 at 19:54
I want to bring back the status column thats why its included. I want the status of the row with the highest date of 2021-07-01. — Austin, Jun 06 '23 at 19:59
Exclusive join, as described here: https://stackoverflow.com/a/123481/1024832 — Marc, Jun 06 '23 at 20:05
you have more than one row with a date of 2020701 and of those rows you have those two unique Status values - so which one of the two do you want? — Stu, Jun 06 '23 at 20:05
There is only one row with '2020-07-01' in the system with a status of 'Expired'. @MintBerryCRUNCH was able to help. Thanks. — Austin, Jun 06 '23 at 20:31
We are of course just guessing as you have not provided any actual sample data only the output you are getting; it's good you have a solution however. — Stu, Jun 06 '23 at 20:33

score 0 · Accepted Answer · answered Jun 06 '23 at 20:03

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;

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

1 Answers1