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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Need an Efficient Way to Rank Rows with Some...

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

Rate Topic

Display Mode

Topic Options

Author

Message

Hari Seldon-821789

Posted Wednesday, November 14, 2012 3:08 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, April 01, 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

David Betteridge

Posted Wednesday, November 14, 2012 3:35 PM

Right there with Babe

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:41 AM
Points: 739, Visits: 1,565

Have a look at the dense_rank() function.

Post #1384905

Hari Seldon-821789

Posted Wednesday, November 14, 2012 3:37 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, April 01, 2013 5:51 PM
Points: 13, Visits: 50

I'll do that. Thanks!

Post #1384906

Hari Seldon-821789

Posted Wednesday, November 14, 2012 4:29 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, April 01, 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

The Dixie Flatline

Posted Thursday, November 15, 2012 12:31 PM

Hall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:41 AM
Points: 3,788, Visits: 5,538

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

Hari Seldon-821789

Posted Thursday, November 15, 2012 12:40 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, April 01, 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

dwain.c

Posted Thursday, November 15, 2012 5:30 PM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 12:42 AM
Points: 2,338, Visits: 3,158

Hari Seldon-821789 (11/15/2012)

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

No loops! No CURSORs! No RBAR! Hoo-uh!

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?

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!

Post #1385403

Hari Seldon-821789

Posted Friday, November 16, 2012 10:08 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, April 01, 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

The Dixie Flatline

Posted Monday, November 19, 2012 8:00 AM

Hall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:41 AM
Points: 3,788, Visits: 5,538

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 »

Permissions