SQL Server Ranking Functions

  • WayneS

    SSC Guru

    Points: 95341

    Comments posted to this topic are about the item SQL Server Ranking Functions

    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

  • Jeff Moden

    SSC Guru

    Points: 994266

    Yowch... the formatting monster hit the code hard on this one. I hope Steve can fix it soon.

    Glad to see you in the saddle again, Wayne. It's too late for me to read through it tonight but you can bet I'll read it in the morning over coffee.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • SQLRNNR

    SSC Guru

    Points: 281210

    Congrats on getting this article out Wayne.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Michael Meierruth

    SSCrazy Eights

    Points: 9991

    Wayne,

    You state what I always wanted to hear stated, i.e. that ROW_NUMBER()'s ORDER BY is not the same as the SELECT's ORDER BY. Yet, always - haven't encountered an exception yet, when you perform a SELECT the output is always in the order specified by ROW_NUMBER()'s ORDER BY without the presence of an ORDER BY in the SELECT.

    How do you explain that?

    Oh yes, those ORDERBY and PARTITIONBY need fixing.:hehe:

  • mister.magoo

    SSC-Forever

    Points: 47068

    Michael Meierruth (4/20/2010)


    Wayne,

    You state what I always wanted to hear stated, i.e. that ROW_NUMBER()'s ORDER BY is not the same as the SELECT's ORDER BY. Yet, always - haven't encountered an exception yet, when you perform a SELECT the output is always in the order specified by ROW_NUMBER()'s ORDER BY without the presence of an ORDER BY in the SELECT.

    How do you explain that?

    Oh yes, those ORDERBY and PARTITIONBY need fixing.:hehe:

    You just have to look at the execution plan to see why they come out in that order without a specific order by on the select.

    You will see a SORT to get the row numbers in the correct sequence. In the absence of any ORDER BY clause, there is no reason for the result set to be sorted again.

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • SW_Lindsay

    SSCommitted

    Points: 1983

    Nice and helpful article - Thanks. Just Curious...

    To select eligible candidates in the first select you say

    select * from @Candidates where MeetsEligibility = convert(bit,1);

    Why do you convert the 1 into a bit? just saying 1 works. I know that the data type for MeetsEligibility is a bit and I'm just curious if there are efficiencies is converting explicitly like this or is it just a readability thing?

    Steve

  • Ken Davis

    SSCarpal Tunnel

    Points: 4329

    Excellent article. It's very readable with good examples.

  • WayneS

    SSC Guru

    Points: 95341

    SW_Lindsay (4/20/2010)


    Nice and helpful article - Thanks. Just Curious...

    To select eligible candidates in the first select you say

    select * from @Candidates where MeetsEligibility = convert(bit,1);

    Why do you convert the 1 into a bit? just saying 1 works. I know that the data type for MeetsEligibility is a bit and I'm just curious if there are efficiencies is converting explicitly like this or is it just a readability thing?

    Steve

    Avoiding an "Implicit conversion". The literal 1 is an integer, resulting in the underlying field being converted to an integer to do the match... if there is an index on this field, it won't be used. Converting the 1 to a bit avoids the implicit conversion, and allows use of an index if one is present.

    Personally, I wish there were system variables @@True and @@False of datatype bit, set to 1/0 respectively.

    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

  • SW_Lindsay

    SSCommitted

    Points: 1983

    Thanks for the reply and I would agree those constants would be a great additions!

  • Andy Lennon

    SSCrazy

    Points: 2354

    Michael Meierruth (4/20/2010)


    Wayne,

    You state what I always wanted to hear stated, i.e. that ROW_NUMBER()'s ORDER BY is not the same as the SELECT's ORDER BY. Yet, always - haven't encountered an exception yet, when you perform a SELECT the output is always in the order specified by ROW_NUMBER()'s ORDER BY without the presence of an ORDER BY in the SELECT.

    How do you explain that?

    i had the same question. is there a performance gain with the explicit conversion?

    nvm, answered already :hehe:

  • busasquatch

    Grasshopper

    Points: 24

    So, I've overcome the inability to display columnar formats in SSRS subreports by using ROW_NUMBER() along with some modulus "hack-a-math". Here's a brief use case.

    Let's say that a customer can have one or more addresses. Using an SSRS subreport, you'd waste a lot of paper displaying these in one column. But, if you could give each address a "RowNumber" and "ColumnNumber", you could drop the addresses into a matrix using RowNumber and ColumnNumber as your groupings.

    This revelation, single handedly, was my greatest self-discovery in SQL code in conjunction with SSRS, if I do say so myself. 😀

    -------------------

    Brian Zive

    Assistant Director, Systems

    Business Intelligence Analyst

    Massachusetts General Hospital

    Development Office

    ___________________________________
    Brian A. Zive
    Assistant Director, Systems
    Business Intelligence Analyst
    Massachusetts General Hospital
    Development Office

  • Thomas LeBlanc

    SSCertifiable

    Points: 7741

    Great article, thanks for writing it and answering people's questions.

    ThomasLL

    Thomas LeBlanc, MVP Data Platform Consultant

  • magarity kerns

    SSCrazy

    Points: 2765

    I see these functions in Oracle queries all the time and only partly understood what was going on - thanks for explaining it the SQL Server way! Now I can translate the Oracleisms!

  • Trey Staker

    SSCarpal Tunnel

    Points: 4736

    Wayne, Great article! It came a long ways. 🙂

    ---------------------------------------------------------------------
    Use Full Links:
    KB Article from Microsoft on how to ask a question on a Forum

  • WayneS

    SSC Guru

    Points: 95341

    Jeff Moden (4/19/2010)


    Yowch... the formatting monster hit the code hard on this one. I hope Steve can fix it soon.

    I think it's all fixed now... all the code is in one block, and missing spaces have been added back in. Sure makes it a lot easier to read now. Thanks Steve!

    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 31 total)

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