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