RANK and DENSE_RANK functions in SQL Server

Written on May 19, 2016

RANK and DENSE_RANK both are windows function. These functions are used to give ranking based on their order and based on their order in respective partition/group. 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 are total of three records, and of these three records first two records are of same rank. Then in case of RANK() function, third row will have rank of 3, while in case of DENSE_RANK() function third row will have rank of 2. So basically DENSE_RANK does not break sequence.

Let’s have an example to get things clear.

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