April 20, 2017 at 10:03 pm
Comments posted to this topic are about the item Window Functions for Row Number
April 20, 2017 at 11:10 pm
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
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
April 21, 2017 at 1:59 am
Stewart "Arturius" Campbell - Thursday, April 20, 2017 11:10 PMNice easy question, thanks JeffHowever, 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.
Igor Micev,My blog: www.igormicev.com
April 21, 2017 at 2:15 am
Igor Micev - Friday, April 21, 2017 1:59 AMStewart "Arturius" Campbell - Thursday, April 20, 2017 11:10 PMNice easy question, thanks JeffHowever, 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
April 21, 2017 at 2:17 am
Igor Micev - Friday, April 21, 2017 1:59 AMStewart "Arturius" Campbell - Thursday, April 20, 2017 11:10 PMNice easy question, thanks JeffHowever, 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.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
April 21, 2017 at 3:08 am
Neil Burton - Friday, April 21, 2017 2:17 AMYou'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.
I spotted it, because it's a type of syntax error I make quite often. For some reason when writing window functions, I tend to put a comma in the OVER clause if I'm not paying attention.
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
April 21, 2017 at 5:52 am
GilaMonster - Friday, April 21, 2017 3:08 AMNeil Burton - Friday, April 21, 2017 2:17 AMYou'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.I spotted it, because it's a type of syntax error I make quite often. For some reason when writing window functions, I tend to put a comma in the OVER clause if I'm not paying attention.
Nice and simple question to end the week. The comma jumped out at me because I've done in more times than I'd like to admit. I can't blame intellisence for it either. 😉
April 21, 2017 at 7:02 am
GilaMonster - Friday, April 21, 2017 3:08 AMNeil Burton - Friday, April 21, 2017 2:17 AMYou'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.I spotted it, because it's a type of syntax error I make quite often. For some reason when writing window functions, I tend to put a comma in the OVER clause if I'm not paying attention.
Nice to know I'm not the only one that seems to instinctively add a comma for some reason. 🙂
April 21, 2017 at 7:03 am
Stewart "Arturius" Campbell - Thursday, April 20, 2017 11:10 PMNice easy question, thanks JeffHowever, 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
Nice catch Stewart. Looks like once the question is posted I can no longer edit. So maybe some super admin on this site can update that.
April 21, 2017 at 7:08 am
GilaMonster - Friday, April 21, 2017 3:08 AMI spotted it, because it's a type of syntax error I make quite often. For some reason when writing window functions, I tend to put a comma in the OVER clause if I'm not paying attention.
Same here. I always want to add a comma in the windows functions and then with Nested CTEs I always seem to forget the comma.
April 21, 2017 at 10:04 am
Jeff Atherton - Friday, April 21, 2017 7:08 AMGilaMonster - Friday, April 21, 2017 3:08 AMI spotted it, because it's a type of syntax error I make quite often. For some reason when writing window functions, I tend to put a comma in the OVER clause if I'm not paying attention.
Same here. I always want to add a comma in the windows functions and then with Nested CTEs I always seem to forget the comma.
Me, too. Thanks for the question, Jeff!
April 28, 2017 at 2:44 am
I almost got it wrong as I didn't see the comma first and got confused so I had to read the options twice. Sometimes the devil is in the details.
/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply