Nice easy question, thanks Jeff
However, there appears to be an inaccuracy in the answer, viz:
if a PARTITION BY clause is used there is no comma between the PARTITION BY and OVER clause
i believe should read:
if a PARTITION BY clause is used there is no comma between the PARTITION BY and ORDER BY clauses
From the BOL, both of these are correct
SELECT
ROW_NUMBER() OVER(PARTITION BY recovery_model_desc ORDER BY name ASC) AS Row#,
name, recovery_model_desc
FROM sys.databases WHERE database_id < 5;
SELECT
ROW_NUMBER() OVER( ORDER BY name ASC) AS Row#,
name, recovery_model_desc
FROM sys.databases WHERE database_id < 5;
The answer of the QotD should be corrected.
You're right in what you say Igor but if you look very carefully there's a comma between the PARTITION BY and the ORDER BY in one of the incorrect answers. As soon as you try to run the incorrect option it throws an error at you. It nearly caught me out because at an under-caffeinated first glance they both appeared correct.
On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537