UncleJimBob (5/12/2009)
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... 🙂
AFAIK, you are correct. There's no reason to include a column in the ORDER BY if it's already in the PARTITION BY, it has no effect.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]