SQL : ROW_NUMBER (), RANK () and DENSE_RANK ()

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

Table:  EmployeeCourse

Select * From EmployeeCourse

Output:  

Employee ID	Course ID
1	1
1	3
2	2
2	1
3	3
3	1

Row_NUMBER ():

This function assign unique ID for each row of result with help of Order by Column.

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() :

This function 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.

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() :

This function 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.

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!!