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.
The QoD is correct as it stands.
ROW_NUMBER() OVER(PARTITION BY recovery_model_desc ORDER BY name ASC) AS Row# is syntatically valid, but was not one of the answers offered. The answer that was offered with both partition by and order by in had a comma somewhere that it does not belong.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass