April 03, 2009

RANK() VS DENSE_RANK() With an Example using SQL Server 2005/2008




The RANK()function in SQL Server returns the position of a value within the partition of a result set, with gaps in the ranking where there are ties.

The DENSE_RANK() function in SQL Server returns the position of a value within the partition of a result set, leaving no gaps in the ranking where there are ties.

Let us understand this difference with an example and then observe the results while using these two functions:

We will run two queries, one using RANK() and the other using DENSE_RANK() and observe the difference in the results. We will be using the ORDERS table of the NORTHWIND database to demonstrate the difference. The query will fetch the list of Customers ordered by the highest number of orders each has placed.

Using the RANK() function


SELECT RANK() OVER (ORDER BY TotCnt DESC) AS TopCustomers, CustomerID, TotCnt


FROM (SELECT CustomerID, COUNT(*) AS TotCnt


FROM Orders Group BY CustomerID) AS Cust




OUTPUT



As shown in the results above, while using the RANK() function, if two or more rows tie for a rank, each tied rows receives the same rank, however with gaps in the ranking where there are ties. For example, Customers 'FOLKO' and 'HUNGO' have the same number of orders(i.e. 19), so they are both ranked 4. The Customers with the next highest order(HILAA, BERGS, RATTC) are ranked number 6 instead of 5, because there are two rows that are ranked higher at 4.

Using the DENSE_RANK() function


SELECT DENSE_RANK() OVER (ORDER BY TotCnt DESC) AS TopCustomers, CustomerID, TotCnt


FROM (SELECT CustomerID, COUNT(*) AS TotCnt


FROM Orders Group BY CustomerID) AS Cust




OUTPUT



As shown in the results above, while using the DENSE_RANK() function, if two or more rows tie for a rank in the same partition, each tied rows receives the same rank, however leaving no gaps in the ranking where there are ties. Customers 'FOLKO' and 'HUNGO' have the same number of orders(i.e. 19), so they are both ranked 4. The Customers with the next highest order(HILAA, BERGS, RATTC) are ranked number 5. This is not the same as the RANK() function where the Customer with the next highest number of orders were ranked number 6.

Well I hope after seeing these example, you will understand the difference between the RANK() and DENSE_RANK() and will know where to use what.


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

 
  Feedback:

comments

10 Responses to "RANK() VS DENSE_RANK() With an Example using SQL Server 2005/2008"
  1. hatic said...
    July 21, 2009 at 10:44 AM

    Hi,
    How can I convert these two rows to access sql?

    dense_rank() over(partition by field1 order by field2) as name1,

    row_number() over(partition by fld1, (dense_rank() over(partition by fldnm1 order by fldnm2)) order by fld2) as name2

    thanks in advance

  2. Suprotim Agarwal said...
    July 22, 2009 at 3:40 AM

    access sql? You mean MSAccess?

  3. hatic said...
    July 22, 2009 at 6:00 AM

    yes, MS Access 2003

  4. Suprotim Agarwal said...
    July 26, 2009 at 10:29 PM

    hatic: Seems you got your answer here. Sharing the link for benefit of readers
    http://www.eggheadcafe.com/conversation.aspx?messageid=34757793&threadid=34757790

  5. Madhivanan said...
    February 3, 2010 at 6:38 AM

    Here is an article about multiple usages of Row_number() function

    http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

    Madhivanan

  6. Jay said...
    July 7, 2011 at 2:27 PM

    Nice explanation of rank() vs. dense_rank(). Thanks!

  7. Anonymous said...
    October 1, 2011 at 2:21 AM

    Very good article.

    See the Rank() and DENSE_RANK() functions examples with following link
    http://www.besttechtools.com/SQLArticles.aspx?ID=Rank

  8. Anonymous said...
    December 18, 2012 at 1:31 AM

    good job thanks lot

  9. Anonymous said...
    September 29, 2013 at 1:40 PM

    Simple and good expanation

  10. Anonymous said...
    November 20, 2013 at 7:31 AM

    Thanks a lot for your article. It was so easy to understand and explained the difference clearly.

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions