Understanding the OVER Clause in SQL Server

  • 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

  • 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

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

    Nice article. thank you.

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

  • Excellent article - thank you.

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

  • Fantastic article.. thanks

  • Great article.

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

  • Is there any stairway about analytics functions?

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

    - Les

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

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • 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

  • 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 24 total)

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