WITH AuditData AS (

SELECT *,

RN = ROW_NUMBER() OVER(PARTITION BY ID ORDER BY [Modified On] DESC),

max([Modified on]) OVER(PARTITION BY ID) AS [Max Modified On]

FROM @Audit

)

SELECT [ID], [Owner], [Modified on], [Value], [Max Modified On]

FROM AuditData AS AD

WHERE RN = 1;