Back to articles list April 2, 2021 - 6 minutes read What Is the RANK() Function in SQL, and How Do You Use It? Kateryna Koidan Kateryna is a data science writer from Kyiv, Ukraine. She worked for BNP Paribas, the leading European banking group, as an internal auditor for more than 6 years. More recently, she decided to pursue only the favorite part of her job—data analysis. Now she is continuing her self-education with deep-learning courses, enjoys coding for data analysis and visualization projects, and writes on the topics of data science and artificial intelligence. Kateryna is also a proud mother of two lovely toddlers, who make her life full of fun. Tags: sql learn sql rank online practice window functions 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: developers idfirst_namelast_namelevelyears_experience 10JackDavisjunior1 11HelenBrownjunior0 12KateWilliamssenior4 13AlexMillersenior8 14SophiaMooremid-level3 15NickJacksonmid-level3 16StevenMartinjunior2 17MeganStevensjunior1 18JohnJonessenior5 19MaxWebermid-level4 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; first_namelast_namelevelyears_experiencerank AlexMillersenior81 JohnJonessenior52 MaxWebermid-level43 KateWilliamssenior43 NickJacksonmid-level35 SophiaMooremid-level35 StevenMartinjunior27 MeganStevensjunior18 JackDavisjunior18 HelenBrownjunior010 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: first_namelast_namelevelyears_experiencerank StevenMartinjunior21 JackDavisjunior12 MeganStevensjunior12 HelenBrownjunior04 MaxWebermid-level41 NickJacksonmid-level32 SophiaMooremid-level32 AlexMillersenior81 JohnJonessenior52 KateWilliamssenior43 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; first_namelast_namelevelyears_experiencerankdense_rankrow_number AlexMillersenior8111 JohnJonessenior5222 MaxWebermid-level4333 KateWilliamssenior4334 NickJacksonmid-level3545 SophiaMooremid-level3546 StevenMartinjunior2757 MeganStevensjunior1868 JackDavisjunior1869 HelenBrownjunior010710 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! Tags: sql learn sql rank online practice window functions You may also like How to Rank Rows in SQL: A Complete Guide Here’s what you need to know about SQL RANK all in one place. Learn about RANK functions with explanations, examples, and common use cases. Read more Overview of Ranking Functions in SQL Do you need to rank rows in SQL? Learn about the SQL ranking functions, their syntax, and the differences between them, and see real-world examples. Read more SQL Course of the Month – Window Functions Find out why you should learn SQL window functions in April and why you should do it in our course. Read more SQL Window Functions Cheat Sheet This 2-page SQL Window Functions Cheat Sheet covers the syntax of window functions and a list of window functions. Download it in PDF or PNG format. Read more How to Practice SQL Window Functions at Home Find out how to approach and structure your SQL window function practice time. Learn about the obstacles you’ll face and how to avoid them. Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.