- ROW_NUMBER()
- RANK()
- DENSE_RANK()
All of these three functions are used to calculate the Id of row but in different way.
I am using the below script for examples
CREATE TABLE Marks
(
SubjectId INT,
Marks INT
)
INSERT INTO Marks VALUES(1,100),
(2,90),
(3,80),
(4,70),
(5,70),
(6,50),
(7,40),
(8,40),
(9,30)
1. ROW_NUMBER():-
This function will assign the unique Id to each row of result.
![](http://findnerd.com/imagedata/4173/4173.jpg)
Please see the result in above image. ROW_NUMBER() has assigned unique Id to each record
2) Rank() :-
This function works in same way as ROW_NUMBER() except the "equal" records are ranked the same. This will leave the gap of between the same values. please see the below result
![](http://findnerd.com/imagedata/4174/4174.jpg)
As we can see Marks with value 70 is assigned the same rank 4 but for the next value it gave rank 6 it skipped the rank 5. Same case is for value 40
3) DENSE_RANK() :-
This function is same like RANK() but it does not leave the gap when assigning the rank. it is “dense”. Please refer the below image
![](http://findnerd.com/imagedata/4175/4175.jpg)
0 Comment(s)