Understanding the OVER Clause in SQL Server

  • pilab72 (10/14/2015)


    Is there any stairway about analytics functions?

    Nope. Except for Part 7[/url] of the Stairway to Advanced T-SQL[/url]

    I highly recommend Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • Agree - Row_Number() is more efficient there

    I simply like to demonstrate how to go over limitations 🙂 with this next value over

    Henn

  • Good article about OVER, thanks.

  • From an operational DBA who doesn't get to play around with this sort of thing, it's still valuable to know.

    Thank you.

    qh

    [font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]
  • The range is actually much more flexible than stated. For example these are all valid ranges:

    BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING

    BETWEEN 10 PRECEDING AND 5 PRECEDING

    BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING

    BETWEEN 10 FOLLOWING AND 20 FOLLOWING

    The only stipulation is that the beginning of the range has to be smaller than the end of the range.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • That's a good point Drew, and a better way of explaining 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

  • Very enlightening for me. Much appreciated.

    Br. Kenneth Igiri
    https://kennethigiri.com
    All nations come to my light, all kings to the brightness of my rising

  • Though I seldom use it, the OVER clause has come in handy at times.

  • Very good post. giving deep understanding of over class

    Sheraz Mirza::hehe:

Viewing 9 posts - 16 through 24 (of 24 total)

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