Window Functions for Row Number

  • Comments posted to this topic are about the item Window Functions for Row Number

  • 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”

  • Stewart "Arturius" Campbell - Thursday, April 20, 2017 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

    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

  • Igor Micev - Friday, April 21, 2017 1:59 AM

    Stewart "Arturius" Campbell - Thursday, April 20, 2017 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

    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
  • Igor Micev - Friday, April 21, 2017 1:59 AM

    Stewart "Arturius" Campbell - Thursday, April 20, 2017 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

    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

  • Neil Burton - Friday, April 21, 2017 2:17 AM

    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.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster - Friday, April 21, 2017 3:08 AM

    Neil Burton - Friday, April 21, 2017 2:17 AM

    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.

    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. 😉

  • GilaMonster - Friday, April 21, 2017 3:08 AM

    Neil Burton - Friday, April 21, 2017 2:17 AM

    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.

    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.  🙂

  • Stewart "Arturius" Campbell - Thursday, April 20, 2017 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

    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.

  • GilaMonster - Friday, April 21, 2017 3:08 AM

    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.

    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.

  • Jeff Atherton - Friday, April 21, 2017 7:08 AM

    GilaMonster - Friday, April 21, 2017 3:08 AM

    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.

    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!

  • 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