-- how about?

SELECT

pv.ProductID

, pv.Version

, pv.MinorVersion

, pv.ReleaseVersion

, pv.StandardCost

FROM Production.ProductVersion pv WITH (NOLOCK)

WHERE EXISTS (

SELECT *

FROM (

SELECT TOP 1

pv2.ProductID

, pv2.Version

, pv2.MinorVersion

, pv2.ReleaseVersion

FROM Production.ProductVersion pv2 WITH (NOLOCK)

WHERE pv2.ProductID = pv.ProductID

ORDER BY

pv2.ProductID DESC

, pv2.Version DESC

, pv2.MinorVersion DESC

, pv2.ReleaseVersion DESC

) pv2

WHERE pv2.ProductID = pv.ProductID

AND pv2.Version = pv.Version

AND pv2.MinorVersion = pv.MinorVersion

AND pv2.ReleaseVersion = pv.ReleaseVersion

)