Support us .Net Basics C# SQL ASP.NET Aarvi MVC Slides C# Programs Subscribe Download

Difference between rank dense_rank and row_number in SQL

Suggested Videos
Part 108 - Over clause in SQL Server
Part 109 - Row_Number function in SQL Server
Part 110 - Rank and Dense_Rank in SQL Server



In this video we will discuss the similarities and difference between RANK, DENSE_RANK and ROW_NUMBER functions in SQL Server.



Similarities between RANK, DENSE_RANK and ROW_NUMBER functions
  • Returns an increasing integer value starting at 1 based on the ordering of rows imposed by the ORDER BY clause (if there are no ties)
  • ORDER BY clause is required
  • PARTITION BY clause is optional
  • When the data is partitioned, the integer value is reset to 1 when the partition changes
We will use the following Employees table for the examples in this video
rank dense_rank row_number in sql server

SQL Script to create the Employees table
Create Table Employees
(
     Id int primary key,
     Name nvarchar(50),
     Gender nvarchar(10),
     Salary int
)
Go

Insert Into Employees Values (1, 'Mark', 'Male', 6000)
Insert Into Employees Values (2, 'John', 'Male', 8000)
Insert Into Employees Values (3, 'Pam', 'Female', 4000)
Insert Into Employees Values (4, 'Sara', 'Female', 5000)
Insert Into Employees Values (5, 'Todd', 'Male', 3000)

Notice that no two employees in the table have the same salary. So all the 3 functions RANK, DENSE_RANK and ROW_NUMBER produce the same increasing integer value when ordered by Salary column.

SELECT Name, Salary, Gender,
ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNumber,
RANK() OVER (ORDER BY Salary DESC) AS [Rank],
DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRank
FROM Employees

row_number vs rank vs dense_rank in sql server

You will only see the difference when there ties (duplicate values in the column used in the ORDER BY clause).

Now let's include duplicate values for Salary column. 

To do this 
First delete existing data from the Employees table
DELETE FROM Employees

Insert new rows with duplicate valuse for Salary column
Insert Into Employees Values (1, 'Mark', 'Male', 8000)
Insert Into Employees Values (2, 'John', 'Male', 8000)
Insert Into Employees Values (3, 'Pam', 'Female', 8000)
Insert Into Employees Values (4, 'Sara', 'Female', 4000)
Insert Into Employees Values (5, 'Todd', 'Male', 3500)

At this point data in the Employees table should be as shown below
Employees table with duplicate salary column

Notice 3 employees have the same salary 8000. When you execute the following query you can clearly see the difference between RANK, DENSE_RANK and ROW_NUMBER functions.

SELECT Name, Salary, Gender,
ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNumber,
RANK() OVER (ORDER BY Salary DESC) AS [Rank],
DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRank
FROM Employees

dense_rank vs rank vs row_number sql server

Difference between RANK, DENSE_RANK and ROW_NUMBER functions
  • ROW_NUMBER : Returns an increasing unique number for each row starting at 1, even if there are duplicates.
  • RANK : Returns an increasing unique number for each row starting at 1. When there are duplicates, same rank is assigned to all the duplicate rows, but the next row after the duplicate rows will have the rank it would have been assigned if there had been no duplicates. So RANK function skips rankings if there are duplicates.
  • DENSE_RANK : Returns an increasing unique number for each row starting at 1. When there are duplicates, same rank is assigned to all the duplicate rows but the DENSE_RANK function will not skip any ranks. This means the next row after the duplicate rows will have the next rank in the sequence.

2 comments:

  1. below query Returns same rankings for all the 3 functions
    because you have used PARTITION BY Name => each name will be treated as one partition in the result set.
    Hence we'll get this type of result.
    So, here the ranking will be based on order By clause, if there is a tie

    ReplyDelete
  2. You are ordering by id and that is unique.
    Also don't partition by name.
    Try using the below query with cte

    SELECT *,
    ROW_NUMBER() OVER(PARTITION BY Gender ORDER BY Salary desc) As RowNumber,
    DENSE_RANK() OVER(PARTITION BY Gender ORDER BY Salary desc) As DenseRank,
    RANK() OVER (PARTITION BY gender ORDER BY Salary desc) As Rank
    FROM tblEmployeesRanking

    ReplyDelete

It would be great if you can help share these free resources