I think this was a good demonstration of where these 2005+ features can be useful. I have a job coming up where this could really help.

IMO, none of the few alternative queries posted so far are as clear in their intent, which I think is almost as important as performance, and an acceptable trade-off where performance isn't dramatically affected.

Slightly off-topic, but I couldn't help being reminded of someone's suggestion I saw recently of a 'FIRST' aggregate (MS Access has one), which of course would make the query really simple, and probably perform really well. I suspect there must be someone who'd done one using CLR:

SELECT ProductID,

FIRST(Version) AS Version,

FIRST(MinorVersion) AS MinorVersion,

FIRST(ReleaseVersion) AS ReleaseVersion,

FIRST(StandardCost) AS StandardCost

FROM Production.ProductVersion

ORDER BY ProductID, Version DESC, MinorVersion DESC, ReleaseVersion DESC

GROUP BY ProductID