Overview of SQL RANK functions
Overview of SQL RANK functions
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 [email protected]
View all posts by Rajendra Gupta Latest posts by Rajendra Gupta (see all) Copy data from AWS RDS SQL Server to Azure SQL Database - October 21, 2022 Rename on-premises SQL Server database and Azure SQL database - October 18, 2022 SQL Commands to check current Date and Time (Timestamp) in SQL Server - October 7, 2022
SQLShack
SQL Server training EspañolOverview of SQL RANK functions
July 3, 2019 by Rajendra Gupta 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. 1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859 CREATE TABLE ExamResult(StudentName VARCHAR(70), Subject VARCHAR(20), Marks INT);INSERT INTO ExamResultVALUES('Lily', 'Maths', 65);INSERT INTO ExamResultVALUES('Lily', 'Science', 80);INSERT INTO ExamResultVALUES('Lily', 'english', 70);INSERT INTO ExamResultVALUES('Isabella', 'Maths', 50);INSERT INTO ExamResultVALUES('Isabella', 'Science', 70);INSERT INTO ExamResultVALUES('Isabella', 'english', 90);INSERT INTO ExamResultVALUES('Olivia', 'Maths', 55);INSERT INTO ExamResultVALUES('Olivia', 'Science', 60);INSERT INTO ExamResultVALUES('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. 12345 SELECT Studentname, Subject, Marks, ROW_NUMBER() OVER(ORDER BY Marks) RowNumberFROM 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. 12345 SELECT Studentname, Subject, Marks, ROW_NUMBER() OVER(ORDER BY Marks desc) RowNumberFROM 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 1234567 SELECT Studentname, Subject, Marks, RANK() OVER(PARTITION BY Studentname ORDER BY Marks DESC) RankFROM ExamResultORDER 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. 123456 SELECT Studentname, Subject, Marks, RANK() OVER(ORDER BY Marks DESC) RankFROM ExamResultORDER 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. 123456 SELECT Studentname, Subject, Marks, DENSE_RANK() OVER(ORDER BY Marks DESC) RankFROM ExamResultORDER 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. 1234567 SELECT Studentname, Subject, Marks, DENSE_RANK() OVER(PARTITION BY Subject ORDER BY Marks DESC) RankFROM ExamResultORDER 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 1234567 SELECT Studentname, Subject, Marks, RANK() OVER(PARTITION BY StudentName ORDER BY Marks ) RankFROM ExamResultORDER BY Studentname, Rank; Query 2 1234567 SELECT 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. 12345 SELECT *, NTILE(2) OVER( ORDER BY Marks DESC) RankFROM ExamResultORDER 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. 12345 SELECT *, NTILE(3) OVER( ORDER BY Marks DESC) RankFROM ExamResultORDER 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. 1234 SELECT *, NTILE(2) OVER(PARTITION BY subject ORDER BY Marks DESC) RankFROM ExamResultORDER 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. 12345678910 WITH StudentRanks AS( SELECT *, ROW_NUMBER() OVER( ORDER BY Marks) AS Ranks FROM ExamResult) SELECT StudentName , Marks FROM StudentRanksWHERE Ranks >= 1 and Ranks <=3ORDER BY Ranks We can use the OFFSET FETCH command starting from SQL Server 2012 to fetch a specific number of records. 123456789 WITH StudentRanks AS( SELECT *, ROW_NUMBER() OVER( ORDER BY Marks) AS Ranks FROM ExamResult) SELECT StudentName , Marks FROM StudentRanksORDER 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. Author Recent Posts Rajendra GuptaHi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.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 [email protected]
View all posts by Rajendra Gupta Latest posts by Rajendra Gupta (see all) Copy data from AWS RDS SQL Server to Azure SQL Database - October 21, 2022 Rename on-premises SQL Server database and Azure SQL database - October 18, 2022 SQL Commands to check current Date and Time (Timestamp) in SQL Server - October 7, 2022