In this article, we will focus on finding Rank using ROW_NUMBER, RANK, DENSERANK. ROW_NUMBER is used for numbering set of rows based on ordering and partitioning. Like ROW_NUMBER we can also use RANK and DENSERANK to find rank of student in a class, in a section moreover in entire school.
To understand, lets take an example of Result table with two columns one for StudentId and Other for Marks.
Following T-Sql will create a table and fill data for students using while loop.
CREATE Table dbo.Result
(
StudentId int,
Marks INT
)
Go
DECLARE @c int = 1
SET NOCOUNT ON;
WHILE @c<=10
BEGIN
INSERT INTO dbo.Result
SELECT @c, CAST((RAND()*100) AS INT)
SET @c+=1
END
Go
SELECT * FROM dbo.Result
Go
OUTPUT: StudentId Marks ----------- ----------- 1 9 2 37 3 48 4 15 5 74 6 53 7 23 8 30 9 79 10 59 (10 rows affected)
Lets find rank of student in class using ROW_NUMBER() By applying ordering by Marks in descending. Means student have more marks will have less number of Rank.
SELECT ROW_NUMBER() OVER(ORDER BY Marks Desc) RowNumberRank, *
FROM dbo.Result
Go
OUTPUT: RowNumberRank StudentId Marks -------------------- ----------- ----------- 1 9 79 2 5 74 3 10 59 4 6 53 5 3 48 6 2 37 7 8 30 8 7 23 9 4 15 10 1 9 (10 rows affected)
We can achieve same output with by RANK() function.
SELECT RANK() OVER(ORDER BY Marks Desc) SectionRank, *
FROM dbo.Result
Go
OUTPUT: SysRank StudentId Marks -------------------- ----------- ----------- 1 9 79 2 5 74 3 10 59 4 6 53 5 3 48 6 2 37 7 8 30 8 7 23 9 4 15 10 1 9 (10 rows affected)
It will works fine, What happens if More than 1 student have same marks ?
Lets update Marks from 74 to 79 for student with StudentId=5
UPDATE dbo.Result SET Marks = 79 WHERE StudentId=5 GoLet again check the class position for student.
SELECT ROW_NUMBER() OVER(ORDER BY Marks Desc) RowNumberRank, * FROM dbo.Result
Go
OUTPUT: RowNumberRank StudentId Marks -------------------- ----------- ----------- 1 5 79 2 9 79 3 10 59 4 6 53 5 3 48 6 2 37 7 8 30 8 7 23 9 4 15 10 1 9 (10 rows affected)
Here StudentId 7,5 have same marks i.e. 95 but Rank calculated 1,2 respectively, Which should be same.
So in this case ROW_NUMBER() give us wrong results.
Lets apply RANK() function.
SELECT RANK() OVER(ORDER BY Marks Desc) SysRank, * FROM dbo.Result
Go
OUTPUT: SysRank StudentId Marks -------------------- ----------- ----------- 1 5 79 1 9 79 3 10 59 4 6 53 5 3 48 6 2 37 7 8 30 8 7 23 9 4 15 10 1 9 (10 rows affected)
Here rank 2 is missing.
Lets take one more example for floating marks.
We will alter table dbo.Result for modifiying data type of marks from int to decimal(5,2).
Delete Data from table and populate table with floating point marks.
ALTER TABLE dbo.Result
ALTER COLUMN Marks Decimal(5,2)
Go
DELETE FROM dbo.Result
Go
DECLARE @c int = 1
SET NOCOUNT ON;
WHILE @c<=10
BEGIN
INSERT INTO dbo.Result
SELECT @c, CAST((RAND()*100) AS decimal(5,2))
SET @c+=1
END
Go
SELECT * FROM dbo.Result
Go
OUTPUT: StudentId Marks ----------- --------- 1 11.01 2 54.10 3 25.36 4 4.72 5 22.03 6 49.57 7 95.09 8 63.25 9 70.52 10 24.10 (10 rows affected)
Let apply ROW_NUMBER() and RANK() Method for decimal type marks.
SELECT ROW_NUMBER() OVER(ORDER BY Marks Desc) RowNumberRank, * FROM dbo.Result
Go
OUTPUT: RowNumberRank StudentId Marks -------------------- ----------- --------- 1 7 95.09 2 9 70.52 3 8 63.25 4 2 54.10 5 6 49.57 6 3 25.36 7 10 24.10 8 5 22.03 9 1 11.01 10 4 4.72 (10 rows affected)
SELECT RANK() OVER(ORDER BY Marks Desc) SysRank, * FROM dbo.Result
Go
OUTPUT: SysRank StudentId Marks -------------------- ----------- ------- 1 7 95.09 2 9 70.52 3 8 63.25 4 2 54.10 5 6 49.57 6 3 25.36 7 10 24.10 8 5 22.03 9 1 11.01 10 4 4.72 (10 rows affected)
For both the functions output will be same and correct. due to unique marks.
Adding some duplicate marks.
INSERT INTO dbo.Result
SELECT TOP 5 StudentId+10,Marks
FROM dbo.Result
Go
SELECT * FROM dbo.Result
Go
OUTPUT: StudentId Marks ----------- --------- 1 11.01 2 54.10 3 25.36 4 4.72 5 22.03 6 49.57 7 95.09 8 63.25 9 70.52 10 24.10 11 11.01 12 54.10 13 25.36 14 4.72 15 22.03 (15 rows affected)
SELECT ROW_NUMBER() OVER(ORDER BY Marks Desc) RowNumberRank, * FROM dbo.Result
Go
OUTPUT: RowNumberRank StudentId Marks -------------------- ----------- --------------------------------------- 1 7 95.09 2 9 70.52 3 8 63.25 4 2 54.10 5 12 54.10 6 6 49.57 7 13 25.36 8 3 25.36 9 10 24.10 10 5 22.03 11 15 22.03 12 1 11.01 13 11 11.01 14 4 4.72 15 14 4.72 (15 rows affected)
Wrong Output obtained for duplicate marks.
SELECT RANK() OVER(ORDER BY Marks Desc) SysRank, * FROM dbo.Result
Go
OUTPUT: SysRank StudentId Marks -------------------- ----------- --------------------------------------- 1 7 95.09 2 9 70.52 3 8 63.25 4 2 54.10 4 12 54.10 6 6 49.57 7 13 25.36 7 3 25.36 9 10 24.10 10 5 22.03 10 15 22.03 12 1 11.01 12 11 11.01 14 4 4.72 14 14 4.72 (15 rows affected)
Again Wrong result even with RANK() function. Missing Rank 5, 8, 11 and 13
So to Get Accurate Rank based on Marks we will use DENSERANK() Function as follows.
SELECT DENSE_RANK() OVER(ORDER BY Marks Desc) SysDenseRank, * FROM dbo.Result
Go
OUTPUT: SysDenseRank StudentId Marks -------------------- ----------- ----------- 1 7 95.09 2 9 70.52 3 8 63.25 4 2 54.10 4 12 54.10 5 6 49.57 6 13 25.36 6 3 25.36 7 10 24.10 8 5 22.03 8 15 22.03 9 1 11.01 9 11 11.01 10 4 4.72 10 14 4.72 (15 rows affected)
So DENSERANK() function returns set of rows with desired ranking position of students.
Suppose if we want to get again unique rank in stead of same like 4, 6, 9 and 10 position on the basis of Students, who get admission first.
SELECT DENSE_RANK() OVER(ORDER BY Marks Desc, StudentId) SysDenseRank, * FROM dbo.Result
Go
OUTPUT: SysDenseRank StudentId Marks -------------------- ----------- ------- 1 7 95.09 2 9 70.52 3 8 63.25 4 2 54.10 5 12 54.10 6 6 49.57 7 3 25.36 8 13 25.36 9 10 24.10 10 5 22.03 11 15 22.03 12 1 11.01 13 11 11.01 14 4 4.72 15 14 4.72 (15 rows affected)
Finally lets combine all the results to understand the differences.
SELECT
ROW_NUMBER() OVER(ORDER BY Marks Desc) RowNumberRank,
RANK() OVER(ORDER BY Marks Desc) SysRank,
DENSE_RANK() OVER(ORDER BY Marks Desc) SysDenseRank,
DENSE_RANK() OVER(ORDER BY Marks Desc, StudentId Desc) SysUniqueDenseRank,
DENSE_RANK() OVER(ORDER BY Marks Desc, StudentId) SysUniqueDenseRank,*
FROM dbo.Result
Go
OUTPUT: RowNumberRank SysRank SysDenseRank SysUniqueDenseRank SysUniqueDenseRank StudentId Marks ---------------- ------------ ---------------- -------------------- -------------------- ----------- -------- 1 1 1 1 1 7 95.09 2 2 2 2 2 9 70.52 3 3 3 3 3 8 63.25 5 4 4 5 4 2 54.10 4 4 4 4 5 12 54.10 6 6 5 6 6 6 49.57 8 7 6 8 7 3 25.36 7 7 6 7 8 13 25.36 9 9 7 9 9 10 24.10 11 10 8 11 10 5 22.03 10 10 8 10 11 15 22.03 13 12 9 13 12 1 11.01 12 12 9 12 13 11 11.01 15 14 10 15 14 4 4.72 14 14 10 14 15 14 4.72 (15 rows affected)
If you have any query or question or topic on which, we might have to write an article for your interest or any kind of suggestion regarding this post, Just feel free to write us, by hit add comment button below or contact via Contact Us form.
Your feedback and suggestions will be highly appreciated. Also try to leave comments from your valid verified email account, so that we can respond you quickly.