What Is the RANK() Function in SQL, and How Do You Use It?

Who are your top 10 customers? Who are the top-performing employees in each department? To answer questions like these, you’ll need to rank the output of your SQL query. Let’s see how the RANK() function assists with ranking results in SQL.

There are many use cases in which you might need to rank rows in SQL. You may want to rank customers based on the sales volume, students based on their exam results, company assets based on their present value, departments based on the number of employees, or users based on their date of registration.

Ranking is an important and common tool in data analysis. In this tutorial, I’ll provide detailed explanations of how to rank rows in SQL.

RANK() Function Introduction

The RANK() function is one of the window functions in SQL. Window functions look at part of the data and compute the results for this part.

The RANK() function, specifically, assigns a rank to each row based on a provided column. RANK() is included in the SELECT statement with the following syntax:

RANK() OVER (ORDER BY column ASC|DESC)

Basically, you add another column to your result set. This column includes the rank of each record based on the order specified after the ORDER BY keyword. This entails specifying (1) the column to use for sorting the rows and (2) whether the order should be ascending or descending.

The first row gets rank 1, and the following rows get higher rankings. If any rows have the same value in the column used for ordering, they are ranked the same. The RANK() function leaves gaps in such cases.

For example, if the first and second rows have the same value in the provided column, they both get the rank 1. Then, the third row gets the rank 3 (i.e., rank 2 will be missing).

To get confident with the RANK() function, check out our interactive Window Functions course. It also covers other ranking functions that have different approaches to processing the same values in the provided column.

Now, let’s see the RANK() function in action.

Basic Ranking

To see how the RANK() function works in practice, we’ll go through an example. Let’s imagine we have a team of developers. We want to rank them based on their experience. Here’s the table we start with:

Now, our task is to show the developers’ first name, last name, seniority level, years of experience, and rank. We want the most experienced developer to get the rank 1.

Here’s our SQL query together with the result set:

SELECT first_name, last_name, level, years_experience,
       RANK() OVER (ORDER BY years_experience DESC)
FROM developers;

To rank the developers based on their experience, we have included the following field in the SELECT statement:

RANK() OVER (ORDER BY years_experience DESC)

We define the order after the ORDER BY clause by specifying the column to use for sorting (years_experience) and the order (descending). Then, we rank the developers based on this order.

We see that the developer with 8 years of experience gets rank 1, the developer with 5 years of experience gets rank 2, and the two developers with 4 years of experience both get rank 3. Note that the RANK() function leaves a gap here, and the next developers get rank 5 (i.e., rank 4 is missing).

Now you have an idea of how the RANK() function works, including how it processes the same values in the column used for sorting. You can find more use cases in this complete guide to ranking rows in SQL.

Now, we will move to more advanced examples.

Ranking Within Different Groups

In our first example, we ranked the entire result set. Now, let’s say we want to have a separate ranking for each of the seniority levels (i.e., junior, mid-level, and senior).

With the RANK() function in SQL, we can do this by adding the PARTITION BY keyword and specifying the column to be used for grouping the records. Here’s our SQL query:

SELECT first_name, last_name, level, years_experience,
       RANK() OVER (PARTITION BY level ORDER BY years_experience    DESC)
FROM developers;

In the OVER() clause, we first define how to group the records (using the level column) and then define how to order records within these groups (based on the years of experience, starting from the most experienced developer).

Here’s the resulting table:

Observe that we have separate rankings for the junior, mid-level, and senior levels. Still, the most experienced developer in each group gets rank 1, and the developers with less experience get higher rankings.

Also, note that while Kate Williams and Max Weber have the same 4 years of experience, Max gets rank 1 as the most experienced mid-level developer, while Kate gets rank 3 as the least experienced senior developer.

Now that you know how PARTITION BY works, it’s time to learn about other ranking functions.

Other Ranking Functions in SQL

To rank rows in SQL, you can also use the DENSE_RANK() and ROW_NUMBER() functions. The difference lies in the way these functions handle the same values.

As we learned earlier, the RANK() function gives the same rank to records with the same value and leaves a gap to account for these records. In contrast:

• the DENSE_RANK() function doesn’t leave a gap;
• the ROW_NUMBER() function ranks all records differently, even if the values are the same.

It’s easier to understand this difference with an example. So, let’s return to our first use case in which we ranked the entire result set. But, this time, we’ll also use DENSE_RANK() and ROW_NUMBER():

SELECT first_name, last_name, level, years_experience,
       RANK() OVER (ORDER BY years_experience DESC),
       DENSE_RANK() OVER (ORDER BY years_experience DESC),
       ROW_NUMBER() OVER (ORDER BY years_experience DESC)
FROM developers;

Similar to the RANK() function, the DENSE_RANK() function gives the same rank to the developers with the same years of experience (e.g., rank 3 to Kate Williams and Max Weber). However, the next rank in the dense_rank column is 4, leaving no gaps in the ranking.

On the other hand, the ROW_NUMBER() function assigns a different number to all of the developers, including the ones with the same years of experience. So, why does Max get rank 3 and Kate rank 4? It’s an arbitrary choice, and you never know which of the records with the same value will be assigned a higher rank.

Now that you know the difference between the ranking functions, you can decide which one is more suitable for your use cases. If you’re not sure, see more examples of the RANK(), DENSE_RANK(), and ROW_NUMBER() functions in this overview article.

Time To Practice Ranking Functions!

Ranking functions are among the most common tools used by data analysts. So, you should feel confident with ranking in SQL. And confidence comes with practice.

Check out this Window Functions course that includes 218 interactive exercises covering ranking functions, analytics functions, advanced statistics computation with different window frames, etc. You can learn more about the course in this article.

And here’s a bonus: this 2-page SQL Window Functions Cheat Sheet will help you practice ranking and other window functions.

Thanks for reading, and happy learning!