Post reply

Understanding the OVER Clause in SQL Server

  • October 13, 2015 at 11:46 pm

    #1833119

    some remarks

    1. V11 (Azure) don't support OVER (ORDER BY) in Aggretates

    2. the NEXT VALUE with OVER is interesting things to play

    I'd like to persist current rank over order by some attribute. Unfortunately OVER isn't supported in UPDATE and MERGE. But there is workoround

    -- use NOrthwind -- :)

    -- /*

    create sequence nr as int start with 1

    -- */ alter sequence nr restart with 1

    go

    alter table products add x int

    go

    update products

    set x = t.n

    from products p

    join (select productid, next value for nr over (order by unitprice) n from products) T

    on p.productid = t.productid

    go

    select * from products

    go

    alter table products drop column x

    drop sequence nr

    with bests

    Henn

  • October 14, 2015 at 12:00 am

    #321905

    Comments posted to this topic are about the item Understanding the OVER Clause in SQL Server

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • October 14, 2015 at 12:10 am

    #1833120

    James and Cynthia must be some pretty epic DBA's @ $3000\hr 🙂

    Nice article. thank you.

  • October 14, 2015 at 12:31 am

    #1833122

    Great explanation particularly on the RANGE and ROWS clause. Thanks very much for this.

    The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url]

    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs[/url]

  • October 14, 2015 at 5:54 am

    #1833193

    Excellent article - thank you.

  • October 14, 2015 at 7:01 am

    #1833214

    Excellent Article! Thanks for posting this. We always find a way to query what we need.

    This will allow ( for me at least ) to be a bit more surgical. I use row_number() frequently but

    was unaware of all the other uses for OVER as well as ROWS & RANGE. Moving forward, I know this will cut down on the lines of code needed... replacing sub queries or CTE's I'd previously use to get different aggregate values.

    Thanks again,

    Bob.

  • October 14, 2015 at 7:02 am

    #1833216

    Fantastic article.. thanks

  • October 14, 2015 at 7:58 am

    #1833245

    Great article.

  • October 14, 2015 at 8:17 am

    #1833251

    Thanks for taking the time to write this. Took me way beyond what I've done with the OVER function.

  • October 14, 2015 at 8:26 am

    #1833255

    Is there any stairway about analytics functions?

  • October 14, 2015 at 12:34 pm

    #1833317

    Thanks so much for the article. I learned a lot.

    - Les

  • October 14, 2015 at 1:26 pm

    #1833327

    Thanks for this clear and concise article, it really helps. I especially appreciated the inclusion of examples of WHY you would use each combination.

  • October 14, 2015 at 2:43 pm

    #1833332

    My turn to "stalk", ol' friend. 😀 Very well done!

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 14, 2015 at 5:54 pm

    #1833356

    Henn,

    I'm afraid that I just don't understand why you're going through the effort to create a sequence, just to have an incrementing number. Why not just use the ROW_NUMBER function?

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • October 14, 2015 at 5:57 pm

    #1833357

    Thanks to all for the kind words of appreciation for this article - it makes it all worthwhile. I'm glad that it has been so well received, and even more glad that you are learning from it.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

Viewing 15 posts - 1 through 15 (of 23 total)

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