Rank() and Dense_Rank() in SQL

The RANK()function in SQL Server returns the position of a value within the partition of a result set, with gaps in the ranking where there are ties.

Using the RANK() function

SELECT RANK() OVER (ORDER BY Department) AS Ranking, Department,Name

FROM Employee

OUTPUT

Here, we see that in Ranking column we have 1 ranking for Banking Department and 3 ranking for Finance Department in this there is a gap for ranking 2 which is replaced by 1, in the same way ranking for IT Department is 6 as 4 and 5 replaced by 3. That means, if two or more rows tie for a rank, each tied rows receives the same rank, however with gaps in the ranking where there are ties. 

The DENSE_RANK() function in SQL Server returns the position of a value within the partition of a result set, leaving no gaps in the ranking where there are ties.

Using the DENSE_RANK() function

SELECT DENSE_RANK() OVER (ORDER BY Department) AS Ranking, Department,Name

FROM Employee

OUTPUT

Here, we see Banking Department has ranking 1 and Finance has ranking 2 as there is no gaps in ranking.That means, if two or more rows tie for a rank, each tied rows receives the same rank, however with gaps in the ranking where there are ties.