Ranking function assigns a rank or sequence number to each row of the returned dataset in a partition. This function is nondeterministic means it may return different results each time it is called on the same dataset with same condition. For example, the GETDATE() is a nondeterministic as it always returns different value where as SUM() is deterministic as it always return same value on a specific dataset. There are four types of ranking function MSSQL Server:
- ROW_NUMBER
- RANK
- NTILE(N)
- DENSE_RANK
1. ROW_NUMBER: It assigns a sequence number to each row serially.
2. RANK: It assigns a sequence number to each unique row. For duplicate value, it assigns same rank and skips next rank for next row.
3. NTILE(N): It groups the data based on supplied parameter and assigns a sequence number to same group.
4. DENSE_RANK: It groups the data based on supplied number and assigns a sequence number to same group.
Let's see an example now:
First create a student table, insert value.
CREATE TABLE Students(
name varchar(20)
,marks int
,grade varchar(2)
)
GO
INSERT INTO Students(name, marks, grade)
VALUES('Bob', 95, 'A+')
,('Joe', 50, 'F')
,('Jack', 89, 'A')
,('Ben', 85, 'A')
,('Holy', 93, 'A+')
,('Zill', 85, 'A')
,('Casper', 89, 'A')
,('Dan', 70, 'C')
,('Carrol', 95, 'A+')
,('Stokes', 70, 'C')
,('Archer', 63, 'D')
Now execute the select query:
SELECT name, marks, grade
,ROW_NUMBER() OVER (ORDER BY marks DESC) AS "Row Number"
,RANK() OVER (ORDER BY marks DESC) AS Rank
,DENSE_RANK() OVER (ORDER BY grade) AS "Dense Rank"
,NTILE(4) OVER (ORDER BY grade) AS Quartile
FROM Students
The above select query will yield below result set.
Let's analyze it:
- ROW_NUMBER - it assigns a sequential value.
- RANK - For unique "marks", it generates a unique rank, for duplicate marks it assigns same rank and skips next rank.
- DENSE_RANK - Assigns same ranks for same "grade" value.
- NTILE - we split the data by "4" groups and assigns same ranks for same group based on grade.
Hope this will explain the ranking functions.