If I've seen correctly through your illogical naming convention and the fact that your column headings are in the wrong order in your desired results, this should work.  But only if you've posted on the wrong forum and you're not actually using SQL Server 2008.

WITH LatestDates AS (
    SELECT
         year
    ,    name
    ,    last_active
    ,    ROW_NUMBER() OVER (PARTITION BY name ORDER BY year DESC) AS YearNo
    ,    ROW_NUMBER() OVER (PARTITION BY name ORDER BY last_active, year DESC) AS StatusNo
    FROM latest_value
    )
, LatestActive AS (
    SELECT
         year
    ,    name
    ,    last_active AS Status
    ,    YearNo
    ,    FIRST_VALUE(year) OVER (PARTITION BY name ORDER BY StatusNo ASC) AS last_active
    FROM LatestDates
    )
SELECT
     year
,    name
,    Status
,    last_active
FROM LatestActive
WHERE YearNo = 1;

John