Post reply

Understanding the OVER Clause in SQL Server

  • October 14, 2015 at 6:50 pm

    #1833359

    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

  • October 15, 2015 at 1:33 am

    #1833378

    Agree - Row_Number() is more efficient there

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

    Henn

  • October 21, 2015 at 7:27 am

    #1834649

    Good article about OVER, thanks.

  • November 26, 2015 at 9:45 am

    #1842533

    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]

  • March 15, 2016 at 4:04 pm

    #1865485

    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

  • March 15, 2016 at 4:40 pm

    #1865490

    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

  • September 30, 2016 at 5:17 pm

    #1904282

    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

  • October 3, 2016 at 11:53 am

    #1904500

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

  • October 5, 2016 at 11:14 am

    #1904989

    Very good post. giving deep understanding of over class

    Sheraz Mirza::hehe:

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

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