RANK and DENSE_RANK Functions in SQL Server

Both RANK and DENSE_RANK are windows functions. These functions are used to give ranking based on their order and based on their order in respective partition/group. Let’s take a look in more detail.

DENSE_RANK: It is an analytic query that calculates the position of a row in a positioned array of rows. The resulted rank is a number beginning from 1. DENSE_RANK is one of the most crucial analytical functions. It gives rank as sequential numbers. It does not hop rank in event of connections. It is a part of Window Functions.

Rank: The RANK function allots the position number per row in a section. It bypasses the number for matching values. Users accomplish analyses on data by utilizing different aggregated functions like Max, Min, and AVG. In this, they obtain a unique output row by utilizing these functions. SQL Server supplies SQL RANK functions to define rank for respective domains as per the categorizations. It yields an aggregated markdown for each row. It is a part of Window Functions.

The important difference between RANK and DENSE_RANK is that they both will allocate the statuses the identical rank depending on how they tumble corresponded to the other values. Yet, RANK will then ignore the next known ranking value whereas DENSE_RANK would always utilize the next documented ranking value.

Both the RANK and DENSE_RANK functions were presented in SQL Server version 2005. These functions are utilized to yield logical order numbers beginning from 1 founded on the sequence of rows assessed by the ORDER BY clause. Both these functions use the PARTITION_BY_CLAUSE.

This clause is basically employed to segment the outcome set into numerous groups. As it is optional, and if users did not define the PARTITION BY clause, then the RANK function will make the whole result set as one section or cluster. The ORDER BY clause is needed and this clause is utilized to specify the series in which each row is going to allocate their RANK i.e. number.

Their syntax is as follows:

RANK() OVER ( [PARTITION BY statements] ORDER BY statements )
DENSE_RANK() OVER ( [PARTITION BY statements] ORDER BY statements )

A Difference

Consider a scenario where there is a total of three records and two of which are of the same rank. Then in the case of a RANK() function, the third row will have a rank of 3, while in the case of the DENSE_RANK() function, the third row will have a rank of 2. So basically DENSE_RANK does not break sequence.

Let’s have an example to get things clear.

Here, as you analyze the result you’ll find that there are two records of rank 1. So in the Rank column for the third-row, it is showing value 3, whereas in the D_Rank column value of the third row is 2.

   

About the Author

ByteScout Team of Writers ByteScout has a team of professional writers proficient in different technical topics. We select the best writers to cover interesting and trending topics for our readers. We love developers and we hope our articles help you learn about programming and programmers.