In an SQL script, how to get one line by clientid with the most current period end and discard the rest - thank you

Transact-SQL
1

The following SQL script reads accounts information out of an SQL database into an Excel spreadsheet:

SELECT TOP (100) PERCENT ClientId, PeriodEnd, Sales

FROM AccountsDataTableWithClient

WHERE (PeriodEnd > CONVERT(DATETIME, '2020-12-31 00:00:00', 102))

ORDER BY PeriodEnd DESC

There could be more than one line by client in the output, i.e. the client has multiple period ends. However, what we need is the first line it finds for a "ClientId" with the most current "PeriodEnd". The rest of the data can be discarded.

Therefore what syntax can I put into the script so that it only takes a single line for a clientid?

Thanks in advance for your help

Ros

2 

;WITH cte_most_recent_rows AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY ClientId ORDER BY PeriodEnd DESC) AS row_num
    FROM AccountsDataTableWithClient
    WHERE (PeriodEnd > CONVERT(DATETIME, '2020-12-31 00:00:00', 102))
)
SELECT ClientId, PeriodEnd, Sales
FROM cte_most_recent_rows
WHERE row_num = 1
ORDER BY PeriodEnd DESC