2 min read

In this post, we are trying out the rank functions in Microsoft SQL Server. The options we have are:

• ROW_NUMBER()
• RANK()
• DENSE_RANK()

Let’s assume we have a tech shop with the following sales table:

With the following query, we can inspect the differences between the ROW_NUMBER(), RANK() and DENSE_RANK(). As you can see, each function can be used with and without PARTITION BY clause.

Code

SELECT Category,
	   Product,
	   UnitSold,
	   ROW_NUMBER() OVER(ORDER BY UnitSold DESC) as ProductRowNumber,
	   ROW_NUMBER() OVER(PARTITION BY Category ORDER BY UnitSold DESC) as ProductRowNumberPartitionBY,
	   RANK() OVER(ORDER BY UnitSold DESC) as Rank,
	   RANK() OVER(PARTITION BY Category ORDER BY UnitSold DESC) as RankPartitionBY,
	   DENSE_RANK() OVER(ORDER BY UnitSold DESC) as DenseRank,
	   DENSE_RANK() OVER(PARTITION BY Category ORDER BY UnitSold DESC) as DenseRankPartitionBY
FROM dbo.sales

Results

What is PARTITION BY?

With PARTITION BY we can use the functions above within a specified group. Let’s say, we would like to see the ranking within each category. What was the best selling product in laptop category for example. To achieve this, we can include a PARTITION BY clause in our function.

ROW_NUMBER()

When we use ROW_NUMBER itself, we get a constantly increasing number from 1 based on our ORDER BY clause (ProductRowNumber column). No matter if we have equal values, it will increase always the number by 1. When we use the PARTITION BY and split the row numbers by categories, it will start over the numbers from 1 when the server finds a new category (ProductRowNumberPartitionBY column).

RANK()

Jump overt to the RANK function. It is almost the same as ROW_NUMBER, with a bit of salt. If it finds two (or more) equal values it gives the same RANK for these lines AND skips the following numbers from the ranking. For example in the Rank column, you can see four products that sold 16 times, so all of these product get rank 3 and the next product gets rank 7.

DENSE_RANK()

If you would like to get rid of skipping numbers, you can use the next function, DENSE_RANK. It behaves the same with equal values to RANK (again, we have 4 products with rank 3), but the next product’s rank will be 4.

Summary

In this post, we looked at the ranking possibilities of SQL Server. Stay tuned for further tips.

Header image is by Joshua Golde on Unsplash