Stairway to Advanced T-SQL Level 7: Ordering Your Data Using Ranking Functions

  • Greg Larsen

    SSC-Insane

    Points: 20585

    Comments posted to this topic are about the item Stairway to Advanced T-SQL Level 7: Ordering Your Data Using Ranking Functions

    Gregory A. Larsen, MVP

  • FairFunk

    SSC Eights!

    Points: 810

    The best high-level explanation of Windowed functions I've read so far! Excellent work.

  • Tony Trus

    SSChasing Mays

    Points: 654

    Good article thank you for contributions this. You have one typo towards the top where you reference zip code 03054 when I believe you mean to say 03064.

  • Larry Huisingh

    SSC Enthusiast

    Points: 153

    Thank you so much for this article! I've seen and even used these functions a couple of times but I never really understood why they worked the way they do. This was a nice bite-sized chunk of information to savor and swallow.

  • Alan Burstein

    SSC Guru

    Points: 61006

    Great article Greg. Concise, informative and to-the-point. I love Window Ranking Functions and could not live with out them.

    One minor correction:

    A ranking value is just a number, which typically starts at 1 and increments by one for each new ranking value.

    This is not true in the case DENSE_RANK - it increments by the preceding number of ties +1; if the preceding value is unique then it increments by 1. And I think it would be more accurate to say that the ranking value always start at 1.

    Its also worth noting that, although these four functions are called "Ranking functions", RANK and DENSE_RANK are slightly different than ROW_NUMBER and NTILE. For example, RANK and DENSE_RANK are always deterministic whereas ROW_NUMBER and NTILE are only deterministic when they are sorted by a column(s) with distinct values. Itzik Ben-Gan talks about this here. That's just my 2 cents.

    Again, great work sir.

    -- Alan Burstein


    Helpful links:Best practices for getting help on SQLServerCentral -- Jeff ModenHow to Post Performance Problems -- Gail ShawNasty fast set-based string manipulation functions:For splitting strings try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL Server 2012+)To split strings based on patterns try PatternSplitCMNeed to clean or transform a string? try NGrams, PatExclude8K, PatReplace8K, DigitsOnlyEE, or Translate8KI 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

  • hfreeman

    SSC-Addicted

    Points: 496

    I second that recommendation to fix PostalCode 03054 to PostalCode 030564.. Just a Typo. That proves people did read the article.

    Hank Freeman
    Senior SQL Server DBA / Data & Solutions Architect
    hfreeman@msn.com
    678-414-0090 (Personal Cell)

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the great article.

  • goldfinger490

    SSC Rookie

    Points: 47

    ROW_NUMBER () OVER ( [ PARTIION BY <partition_expressions> ] ORDER BY <order_by_column> )

    typo

Viewing 8 posts - 1 through 8 (of 8 total)

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