Rajendra Gupta
difference between RANK() and DENSE_RANK() functions

Overview of SQL RANK functions

July 3, 2019 by

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.

We have the following sample data in the ExamResult table.

Sample Data

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.

ROW_Number() SQL Rank function

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.

ROW_Number() example

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.

RANK()SQL Rank Function

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

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.

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.

Output of RANK() function for similar values

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.

In the output, you can see we have the same rank for both Lily and Isabella who scored 70 marks.

DENSE_RANK()SQL Rank function

Let’s use DENSE_RANK function in combination with the SQL PARTITION BY clause.

We do not have two students with similar marks; therefore result set similar to RANK Function in this case.

Output of DENSE_RANK() function

Let’s update the student mark with the following query and rerun the query.

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.

Output of DENSE_RANK() function

Let’s see the difference between RANK() and DENSE_RANK() SQL Rank function with the following query.

  • Query 1
  • Query 2

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.

difference between RANK() and DENSE_RANK()

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.

difference between RANK() and DENSE_RANK() functions

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.

In the output, we can see two groups. Group 1 contains five rows, and Group 2 contains four rows.

NTILE(N) SQL Rank function

Similarly, NTILE(3) divides the number of rows of three groups having three records in each group.

NTILE(N) function with partition

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.

Output of NTILE(N)  function with partition

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.

CTE to fetch data using SQL RANK functions

We can use the OFFSET FETCH command starting from SQL Server 2012 to fetch a specific number of records.

OFFSET FETCH command

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.

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta
2,489 Views