We perform calculations on data using various aggregated functions such as Max, Min, and AVG. We get a single output row using these functions. SQL Sever provides SQL RANK functions to specify rank for individual fields as per the categorizations. It returns an aggregated value for each participating row. SQL RANK functions also knows as Window Functions.
- Note: Windows term in this does not relate to the Microsoft Windows operating system. These are SQL RANK functions.
We have the following rank functions.
- ROW_NUMBER()
- RANK()
- DENSE_RANK()
- NTILE()
In the SQL RANK functions, we use the OVER() clause to define a set of rows in the result set. We can also use SQL PARTITION BY clause to define a subset of data in a partition. You can also use Order by clause to sort the results in a descending or ascending order.
Before we explore these SQL RANK functions, let’s prepare sample data. In this sample data, we have exam results for three students in Maths, Science and English subjects.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 |
CREATE TABLE ExamResult (StudentName VARCHAR(70), Subject VARCHAR(20), Marks INT ); INSERT INTO ExamResult VALUES ('Lily', 'Maths', 65 ); INSERT INTO ExamResult VALUES ('Lily', 'Science', 80 ); INSERT INTO ExamResult VALUES ('Lily', 'english', 70 ); INSERT INTO ExamResult VALUES ('Isabella', 'Maths', 50 ); INSERT INTO ExamResult VALUES ('Isabella', 'Science', 70 ); INSERT INTO ExamResult VALUES ('Isabella', 'english', 90 ); INSERT INTO ExamResult VALUES ('Olivia', 'Maths', 55 ); INSERT INTO ExamResult VALUES ('Olivia', 'Science', 60 ); INSERT INTO ExamResult VALUES ('Olivia', 'english', 89 ); |
We have the following sample data in the ExamResult table.
Let’s use each SQL Rank Functions in upcoming examples.
ROW_Number() SQL RANK function
We use ROW_Number() SQL RANK function to get a unique sequential number for each row in the specified data. It gives the rank one for the first row and then increments the value by one for each row. We get different ranks for the row having similar values as well.
Execute the following query to get a rank for students as per their marks.
|
1 2 3 4 5 |
SELECT Studentname, Subject, Marks, ROW_NUMBER() OVER(ORDER BY Marks) RowNumber FROM ExamResult; |
By default, it sorts the data in ascending order and starts assigning ranks for each row. In the above screenshot, we get ROW number 1 for marks 50.
We can specify descending order with Order By clause, and it changes the RANK accordingly.
|
1 2 3 4 5 |
SELECT Studentname, Subject, Marks, ROW_NUMBER() OVER(ORDER BY Marks desc) RowNumber FROM ExamResult; |
RANK() SQL RANK Function
We use RANK() SQL Rank function to specify rank for each row in the result set. We have student results for three subjects. We want to rank the result of students as per their marks in the subjects. For example, in the following screenshot, student Isabella got the highest marks in English subject and lowest marks in Maths subject. As per the marks, Isabella gets the first rank in English and 3rd place in Maths subject.
Execute the following query to get this result set. In this query, you can note the following things:
- We use PARTITION BY Studentname clause to perform calculations on each student group
- Each subset should get rank as per their Marks in descending order
- The result set uses Order By clause to sort results on Studentname and their rank
|
1 2 3 4 5 6 7 |
SELECT Studentname, Subject, Marks, RANK() OVER(PARTITION BY Studentname ORDER BY Marks DESC) Rank FROM ExamResult ORDER BY Studentname, Rank; |
Let’s execute the following query of SQL Rank function and look at the result set. In this query, we did not specify SQL PARTITION By clause to divide the data into a smaller subset. We use SQL Rank function with over clause on Marks clause ( in descending order) to get ranks for respective rows.
|
1 2 3 4 5 6 |
SELECT Studentname, Subject, Marks, RANK() OVER(ORDER BY Marks DESC) Rank FROM ExamResult ORDER BY Rank; |
In the output, we can see each student get rank as per their marks irrespective of the specific subject. For example, the highest and lowest marks in the complete result set are 90 and 50 respectively. In the result set, the highest mark gets RANK 1, and the lowest mark gets RANK 9.
If two students get the same marks (in our example, ROW numbers 4 and 5), their ranks are also the same.
DENSE_RANK() SQL RANK function
We use DENSE_RANK() function to specify a unique rank number within the partition as per the specified column value. It is similar to the Rank function with a small difference.
In the SQL RANK function DENSE_RANK(), if we have duplicate values, SQL assigns different ranks to those rows as well. Ideally, we should get the same rank for duplicate or similar values.
Let’s execute the following query with the DENSE_RANK() function.
|
1 2 3 4 5 6 |
SELECT Studentname, Subject, Marks, DENSE_RANK() OVER(ORDER BY Marks DESC) Rank FROM ExamResult ORDER BY Rank; |
In the output, you can see we have the same rank for both Lily and Isabella who scored 70 marks.
Let’s use DENSE_RANK function in combination with the SQL PARTITION BY clause.
|
1 2 3 4 5 6 7 |
SELECT Studentname, Subject, Marks, DENSE_RANK() OVER(PARTITION BY Subject ORDER BY Marks DESC) Rank FROM ExamResult ORDER BY Studentname, Rank; |
We do not have two students with similar marks; therefore result set similar to RANK Function in this case.
Let’s update the student mark with the following query and rerun the query.
|
1 |
Update Examresult set Marks=70 where Studentname='Isabella' and Subject='Maths' |
We can see that in the student group, Isabella got similar marks in Maths and Science subjects. Rank is also the same for both subjects in this case.
Let’s see the difference between RANK() and DENSE_RANK() SQL Rank function with the following query.
- Query 1
1234567SELECT Studentname,Subject,Marks,RANK() OVER(PARTITION BY StudentName ORDER BY Marks ) RankFROM ExamResultORDER BY Studentname,Rank;
- Query 2
1234567SELECT Studentname,Subject,Marks,DENSE_RANK() OVER(PARTITION BY StudentName ORDER BY Marks ) RankFROM ExamResultORDER BY Studentname,Rank;
In the output, you can see a gap in the rank function output within a partition. We do not have any gap in the DENSE_RANK function.
In the following screenshot, you can see that Isabella has similar numbers in the two subjects. A rank function assigns rank 1 for similar values however, internally ignores rank two, and the next row gets rank three.
In the Dense_Rank function, it maintains the rank and does not give any gap for the values.
NTILE(N) SQL RANK function
We use the NTILE(N) function to distribute the number of rows in the specified (N) number of groups. Each row group gets its rank as per the specified condition. We need to specify the value for the desired number of groups.
In my example, we have nine records in the ExamResult table. The NTILE(2) shows that we require a group of two records in the result.
|
1 2 3 4 5 |
SELECT *, NTILE(2) OVER( ORDER BY Marks DESC) Rank FROM ExamResult ORDER BY rank; |
In the output, we can see two groups. Group 1 contains five rows, and Group 2 contains four rows.
Similarly, NTILE(3) divides the number of rows of three groups having three records in each group.
|
1 2 3 4 5 |
SELECT *, NTILE(3) OVER( ORDER BY Marks DESC) Rank FROM ExamResult ORDER BY rank; |
We can use SQL PARTITION BY clause to have more than one partition. In the following query, each partition on subjects is divided into two groups.
|
1 2 3 4 |
SELECT *, NTILE(2) OVER(PARTITION BY subject ORDER BY Marks DESC) Rank FROM ExamResult ORDER BY subject, rank; |
Practical usage of SQL RANK functions
We can use SQL RANK function to fetch specific rows from the data. Suppose we want to get the data of the students from ranks 1 to 3. In the following query, we use common table expressions(CTE) to get data using ROW_NUMBER() function and later filtered the result from CTE to satisfy our condition.
|
1 2 3 4 5 6 7 8 9 10 |
WITH StudentRanks AS ( SELECT *, ROW_NUMBER() OVER( ORDER BY Marks) AS Ranks FROM ExamResult ) SELECT StudentName , Marks FROM StudentRanks WHERE Ranks >= 1 and Ranks <=3 ORDER BY Ranks |
We can use the OFFSET FETCH command starting from SQL Server 2012 to fetch a specific number of records.
|
1 2 3 4 5 6 7 8 9 |
WITH StudentRanks AS ( SELECT *, ROW_NUMBER() OVER( ORDER BY Marks) AS Ranks FROM ExamResult ) SELECT StudentName , Marks FROM StudentRanks ORDER BY Ranks OFFSET 1 ROWS FETCH NEXT 3 ROWS ONLY; |
A quick summary of SQL RANK Functions
ROW_Number | It assigns the sequential rank number to each unique record. |
RANK | It assigns the rank number to each row in a partition. It skips the number for similar values. |
Dense_RANK | It assigns the rank number to each row in a partition. It does not skip the number for similar values. |
NTILE(N) | It divides the number of rows as per specified partition and assigns unique value in the partition. |
Conclusion
In this article, we explored SQL RANK functions and difference between these functions. It is helpful for sql developers to be familiar with these functions to explore and manage their data well. If you have any comments or questions, feel free to leave them in the comments below.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023