Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need an Efficient Way to Rank Rows with Some Rows Having the Same Rank Expand / Collapse
Author
Message
Posted Wednesday, November 14, 2012 3:08 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 1, 2013 5:51 PM
Points: 13, Visits: 50
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

Post #1384896
Posted Wednesday, November 14, 2012 3:35 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, December 18, 2014 1:07 PM
Points: 941, Visits: 1,766
Have a look at the dense_rank() function.
Post #1384905
Posted Wednesday, November 14, 2012 3:37 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 1, 2013 5:51 PM
Points: 13, Visits: 50
I'll do that. Thanks!
Post #1384906
Posted Wednesday, November 14, 2012 4:29 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 1, 2013 5:51 PM
Points: 13, Visits: 50
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!
Post #1384921
Posted Thursday, November 15, 2012 12:31 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 2,787, Visits: 6,098
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? -- Stephen Stills
Post #1385313
Posted Thursday, November 15, 2012 12:40 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 1, 2013 5:51 PM
Points: 13, Visits: 50
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.
Post #1385317
Posted Thursday, November 15, 2012 5:30 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:53 PM
Points: 3,438, Visits: 5,390
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1385403
Posted Friday, November 16, 2012 10:08 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 1, 2013 5:51 PM
Points: 13, Visits: 50
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.
Post #1385769
Posted Monday, November 19, 2012 8:00 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 2,787, Visits: 6,098
So did I.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? -- Stephen Stills
Post #1386415
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse