Post reply

Need an Efficient Way to Rank Rows with Some Rows Having the Same Rank

  • November 14, 2012 at 3:08 pm

    #284948

    Does anyone have a suggeston on the most efficient way to do this ranking? I need to add an incremental rank on each row of a temp table based on the value in 2 fields. If the values in both fields are the same on multiple rows, the rank on those rows should also be the same. The rank should not increment until the value of either field changes in a subsequent row. I've pasted an example below. There are multiple rows with a ranking of 2, since the values in both fields are identical. The table has a primary sort on Value1 and a secondary sort on Value2.

    Rank Value1 Value2

    1 A B

    2 A C

    2 A C

    3 B C

    4 B D

    Any suggestions would be appreciated.

    Thanks,

    Hari

  • November 14, 2012 at 3:35 pm

    #1559161

    Have a look at the dense_rank() function.

  • November 14, 2012 at 3:37 pm

    #1559162

    I'll do that. Thanks!

  • November 14, 2012 at 4:29 pm

    #1559174

    This worked perfectly! In case there's anyone else out there who's never used this function before, here's the statement that I used:

    DENSE_RANK() OVER (ORDER BY Value1, Value2) AS CURRENT_RANK

    There's also a PARTITION BY option for this function, but I didn't need it in this case.

    Thanks again!

  • November 15, 2012 at 12:32 pm

    #1559490

    So Hari, how is that Psychohistory research project going?

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • November 15, 2012 at 12:40 pm

    #1559494

    You're the first person who's ever commented on the name, but then I don't post a whole lot of questions. I'm also known as Demerzel on other sites. Ironically (or maybe intentionally?) both characters are male and I'm not.

  • November 15, 2012 at 5:30 pm

    #1559563

    Hari Seldon-821789 (11/15/2012)

    You're the first person who's ever commented on the name, but then I don't post a whole lot of questions. I'm also known as Demerzel on other sites. Ironically (or maybe intentionally?) both characters are male and I'm not.

    I thought that name looked familiar. Isaac Asimov's Foundation series, right?

    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • November 16, 2012 at 10:08 am

    #1559870

    Yep, that's right. I didn't want to use my real name, so I decided to select an alias from one of my favorite books.

  • November 19, 2012 at 8:00 am

    #1560381

    So did I. 😎

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

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

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