Or maybe this:
SELECT lv.*, la.*
FROM (
SELECT *, rn = ROW_NUMBER() OVER(PARTITION BY [name] ORDER BY [year] DESC)
FROM #latest_value
) lv
OUTER APPLY (
SELECT [Status] = MAX([year])
FROM #latest_value lvi
WHERE lvi.name = lv.name AND lvi.last_active = 'active'
) la
WHERE lv.rn = 1
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden