Window Functions for Row Number

  • Jeff Atherton

    SSCrazy

    Points: 2663

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

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71854

    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”

  • Igor Micev

    SSC-Dedicated

    Points: 33109

    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

  • Gail Shaw

    SSC Guru

    Points: 1004484

    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
  • Neil Burton

    SSC-Insane

    Points: 22226

    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

  • Gail Shaw

    SSC Guru

    Points: 1004484

    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
  • Ed Wagner

    SSC Guru

    Points: 286982

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

  • sestell1

    SSChampion

    Points: 10230

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

  • Jeff Atherton

    SSCrazy

    Points: 2663

    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.

  • Jeff Atherton

    SSCrazy

    Points: 2663

    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.

  • Revenant

    SSC-Forever

    Points: 42467

    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!

  • SQLWinther

    SSCertifiable

    Points: 5946

    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 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply