SQL - The Difference between ROW_NUMBER (), RANK () and DENSE_RANK ()

In this blog, we will discuss about ROW_NUMBER (), RANK () and DENSE_RANK () sql function and these function are used to generate RowID for given result set but these calculate rowID in different way.

Here is EmployeeCourse SQL relational table  and this table stores the empoyee and assigned course mapping information.

Table:  EmployeeCourse
             { 
                  EmployeeID
                  CourseID
              }

Select * From EmployeeCourse

Output:  

EmployeeID	CourseID
1	1
1	3
2	2
2	1
3	3
3	1

Row_NUMBER () assign unique ID for each row of result with help of Order by Column.

SQL Script :

Select *, Row_Number() Over (Order by EmployeeID)  as [Row Number] From EmployeeCourse

Output:

Employee ID	Course ID	Row Number
1	1	1
1	3	2
2	2	3
2	1	4
3	3	5
3	1	6

RANK()  assigns a unique number for each row and for those rows, which have duplicate value, assign the same ranking. There would be gap in the sequence in case of duplicate value.

SQL Script :

Select *, Rank() Over (Order by EmployeeID)  as [Rank] From EmployeeCourse

Output: 

Employee ID	Course ID	Rank
1	1	1
1	3	1
2	2	3
2	1	3
3	3	5
3	1	5

DENSE_RANK()  assigns a unique number for each row and in case of duplicate value, it assigns the same rank but don’t leave any GAP in sequence.

SQL Script :

Select *, DENSE_RANK() Over (Order by EmployeeID)  as [Dense Rank]  From EmployeeCourse

Output:

Employee ID	Course ID	Dense Rank
1	1	1
1	3	1
2	2	2
2	1	2
3	3	3
3	1	3

Thanks for visiting!!