SQL Server Rank Functions

Introduction

As like an Aggregate functions, the Ranking functions will rank the values of specified field and categorize them according to their rank . Ranking functions are most commonly used to find the top records based on conditions, example, to find the top ranking students, top highest paid employees and etc.

Ranking Functions

There are four types of ranking functions in SQL Server as,

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

Example Table

For this, lets take [Details].[Products] table as an example, and its result is as,

Product table result

RANK()

This function is used to give unique rank to specific records. If the record has same value, then RANK() function will assign same rank and skip the next rank. That is, if there are two identical values at rank 2, it will assign the same rank 2 to both records and then skip rank 3 and assign rank 4 to the next record.

Example

SELECT  ProductName, Rate,
RANK() OVER(ORDER BY Rate DESC)[Rank]
FROM [Details].[Products]

Fig. 1 Ranking Function Result

DENSE_RANK()

The DENSE_RANK() function is similar to the RANK() function except that it does not skip any rank. That is, if two identical records are found then DENSE_RANK() will assign the same rank to both records but not skip then skip the next rank.

Example

SELECT  ProductName, Rate,
DENSE_RANK() OVER(ORDER BY Rate DESC)[Rank]
FROM [Details].[Products]

Fig.2 Dense Ranking Functions

ROW_NUMBER()

These functions assign a unique row number to each record.

Example

SELECT  ProductName, Rate,
ROW_NUMBER() OVER(ORDER BY Rate DESC)[Row Number]
FROM [Details].[Products]

Fig. 3 Row Number Function

NTILE()

This function will identify what percentile a given row falls into. That is, if we have 100 rows and we want to create 4 quartiles based on a specified value field we can do so easily and see how many rows fall into each quartile

Example

SELECT  ProductName, Rate,
NTILE(4) OVER(ORDER BY Rate DESC)[Rank]
FROM [Details].[Products]

Fig.4 NTILE Function

Conclusion

In this article, we have discussed about the ranking windows functions in SQL Server. I hope you all found this article much useful. We will discuss more concepts in our upcoming articles. Please share your feedbacks in the comment section.