The following query will work for you:
SELECT T.* FROM TestT T
JOIN (SELECT ID, State, MAX(AsOfDate) AsOfDate FROM TestT
GROUP BY ID, State) A ON T.Id = A.Id AND T.AsOfDate = A.AsOfDate
WHERE T.id = 2023
AND Status = 'A'
In this script TestT is the actual table which contain your data. Change the T.Id as per your need.
Hope this will help you.