Understanding the OVER Clause in SQL Server

  • henn

    SSC Veteran

    Points: 216

    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

  • WayneS

    SSC Guru

    Points: 95374

    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

  • LDK

    SSC Enthusiast

    Points: 186

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

    Nice article. thank you.

  • Felix Pamittan

    Mr or Mrs. 500

    Points: 535

    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]

  • Michael Lysons

    SSCertifiable

    Points: 6478

    Excellent article - thank you.

  • BobMcClellan

    Old Hand

    Points: 399

    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.

  • Anthony peiris

    SSC Enthusiast

    Points: 199

    Fantastic article.. thanks

  • Mike Good

    SSCertifiable

    Points: 7387

    Great article.

  • RonKyle

    SSC-Dedicated

    Points: 31482

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

  • pilab72

    SSC Enthusiast

    Points: 161

    Is there any stairway about analytics functions?

  • lnoland

    SSCommitted

    Points: 1733

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

    - Les

  • johnbergholm

    SSC Journeyman

    Points: 85

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

  • Jeff Moden

    SSC Guru

    Points: 996619

    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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • WayneS

    SSC Guru

    Points: 95374

    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

  • WayneS

    SSC Guru

    Points: 95374

    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