How to Use SQL RANK and DENSE_RANK Functions
If you've been practicing your SQL religiously, like I suggested in Top Skills to Ace Every SQL Interview Question, then you've probably…
A Tutorial on why you would want to use them and their main differences
If you’ve been practicing your SQL religiously, like I suggested in Top Skills to Ace Every SQL Interview Question, then you’ve probably run into problems that use window functions. Or you’ve run into them in the discussions or solutions page on Leetcode.
Window functions can be complicated but they make many problems a lot easier to solve. Once you learn the most used window functions, you will be implementing them into your solutions like a breeze.
Today I’m going to talk about two functions that are helpful in any type of ranking problem—RANK and DENSE_RANK. While these functions are similar, they do have their differences. In a lot of scenarios, both will work to solve your problem. However, it is still important you know the difference for when you do require a special solution.
What they do
RANK and DENSE_RANK are used to order values and assign them numbers depending on where they fall in relation to one another.
For example, let’s say you have 3 students with 3 different test scores- one student received a 100, another received an 85, and the last received a 72. These functions will assign a 1, 2, or 3 to each student depending on the order you wish to rank them.
How to use them
Both functions use an OVER() clause along with PARTITION BY and ORDER BY. The PARTITION BY part of the functions is optional but the ORDER BY is always necessary.
SELECT student_name, RANK() OVER(ORDER BY grades DESC) AS grade_ranking
Here you can see the highest grade is ranked with a 1 and the lowest with a 5 since they are in descending order.
ORDER BY specifies the column whose values you wish to rank. In the example earlier, grades would be specified after ORDER BY. You can order by descending or ascending values.
In the above code snippet we ordered from highest to lowest grade (DESC), below we are ordering from lowest to highest grade (ASC).
SELECT student_name, RANK() OVER(ORDER BY grades ASC) AS grade_ranking
Here you can see the lowest grade is given a rank of 1 and the highest is given a rank of 5 since the grades are in ascending order.
PARTITION BY groups the rankings. When the value changes for the column specified here, the rankings start all over again. Let’s say we added subject to the student’s test scores. If you partitioned the data by subject, it would give you the ranking of each score, grouped by subject.
SELECT student_name, DENSE_RANK() OVER(PARTITION BY subject ORDER BY grades DESC) AS grade_ranking
As you can see, the top grade in each subject is given a ranking of 1 and the rest of the scores are also ranked depending on where they fall in their subject.
Differences between RANK and DENSE_RANK
The difference between these two functions comes down to how they handle identical values. Let’s say we have two students who have the same grade; both scored 90s on their math test.
RANK and DENSE_RANK will assign the grades the same rank depending on how they fall compared to the other values. However, RANK will then skip the next available ranking value whereas DENSE_RANK would still use the next chronological ranking value.
So with RANK, if the two 90s are given a ranking of 2, the next lowest value would be assigned a rank of 4, skipping over 3. With DENSE_RANK, the next lowest value would be assigned a rank of 3, not skipping over any values.
Let’s compare the outcomes of both of these functions.
SELECT student_name, RANK() OVER(ORDER BY grades DESC) AS rank_w_rank, DENSE_RANK() OVER(ORDER BY grades DESC) AS rank_w_dense_rank
Again, you can see there is no rank 2 in the column using RANK in contrast to the DENSE_RANK column which contains rank 2 and ends with rank 4 despite there being 5 rows in the table.
Hopefully you now understand how to use RANK and DENSE_RANK and when to use each. Typically, I use DENSE_RANK as my default rank function in SQL. I find more problems want you to go in chronological ranking order without skipping a number. However, make sure you read the problem carefully and think about the output you’re trying to achieve.
If you are studying for a SQL interview, be sure to check out my article Top Skills to Ace Every SQL Interview Question.
Happy querying!
Learn more about SQL and other tools used by analytics engineers by subscribing to my email list.
Share This Article
Towards Data Science is a community publication. Submit your insights to reach our global audience and earn through the TDS Author Payment Program.
Write for TDS
