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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Website Built with WordPress.com.

Up ↑

%d bloggers like this: