I have seen similar examples of ROW_NUMBER() usage and the following has always puzzled me:

Why do we include columns in the ORDER BY when they are already in the PARTITION BY ?

i.e. I would expect

SELECT ROW_NUMBER() OVER(PARTITION BY ProductID,

Version ORDER BY ProductID, Version, MinorVersion DESC, ReleaseVersion DESC) AS MaxVersion

to be equivalent to

SELECT ROW_NUMBER() OVER(PARTITION BY ProductID,

Version ORDER BY MinorVersion DESC, ReleaseVersion DESC) AS MaxVersion

I know I must be missing obvious with this but I'm hanged if I can figure out what it is... 🙂