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.


About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook

12 comments:

hatic said...

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

Suprotim Agarwal said...

access sql? You mean MSAccess?

hatic said...

yes, MS Access 2003

Suprotim Agarwal said...

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

Unknown said...

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

Jay Philip Williams said...

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

Anonymous said...

Very good article.

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

Anonymous said...

good job thanks lot

Anonymous said...

Simple and good expanation

Anonymous said...

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

Unknown said...

How to count the rank only once in the above query?

Aditya Ghosh said...

Helpful... nicely explained