SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) / Need an Efficient Way to Rank Rows with Some Rows Having the Same Rank / Latest PostsInstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 20:55:59 GMT20RE: Need an Efficient Way to Rank Rows with Some Rows Having the Same Rankhttp://www.sqlservercentral.com/Forums/Topic1384896-392-1.aspxSo did I. :cool:Mon, 19 Nov 2012 08:00:33 GMTThe Dixie FlatlineRE: Need an Efficient Way to Rank Rows with Some Rows Having the Same Rankhttp://www.sqlservercentral.com/Forums/Topic1384896-392-1.aspxYep, 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.Fri, 16 Nov 2012 10:08:54 GMTHari Seldon-821789RE: Need an Efficient Way to Rank Rows with Some Rows Having the Same Rankhttp://www.sqlservercentral.com/Forums/Topic1384896-392-1.aspx[quote][b]Hari Seldon-821789 (11/15/2012)[/b][hr]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.[/quote]I thought that name looked familiar. Isaac Asimov's Foundation series, right?Thu, 15 Nov 2012 17:30:24 GMTdwain.cRE: Need an Efficient Way to Rank Rows with Some Rows Having the Same Rankhttp://www.sqlservercentral.com/Forums/Topic1384896-392-1.aspxYou'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.Thu, 15 Nov 2012 12:40:00 GMTHari Seldon-821789RE: Need an Efficient Way to Rank Rows with Some Rows Having the Same Rankhttp://www.sqlservercentral.com/Forums/Topic1384896-392-1.aspxSo Hari, how is that Psychohistory research project going?Thu, 15 Nov 2012 12:31:59 GMTThe Dixie FlatlineRE: Need an Efficient Way to Rank Rows with Some Rows Having the Same Rankhttp://www.sqlservercentral.com/Forums/Topic1384896-392-1.aspxThis 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_RANKThere's also a PARTITION BY option for this function, but I didn't need it in this case.Thanks again!Wed, 14 Nov 2012 16:29:25 GMTHari Seldon-821789RE: Need an Efficient Way to Rank Rows with Some Rows Having the Same Rankhttp://www.sqlservercentral.com/Forums/Topic1384896-392-1.aspxI'll do that. Thanks!Wed, 14 Nov 2012 15:37:11 GMTHari Seldon-821789RE: Need an Efficient Way to Rank Rows with Some Rows Having the Same Rankhttp://www.sqlservercentral.com/Forums/Topic1384896-392-1.aspxHave a look at the dense_rank() function.Wed, 14 Nov 2012 15:35:54 GMTDavid BetteridgeNeed an Efficient Way to Rank Rows with Some Rows Having the Same Rankhttp://www.sqlservercentral.com/Forums/Topic1384896-392-1.aspxDoes 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 DAny suggestions would be appreciated. Thanks,HariWed, 14 Nov 2012 15:08:13 GMTHari Seldon-821789