When to use DENSE_RANK instead of SQL RANK

Problem

Recently, a developer asked for my input on solving a complex SQL query problem. As we went through each step, I suggested using a ranking function at one point, specifically RANK. However, they were having trouble deciding when to use RANK versus DENSE_RANK in a query. It raises a good question: When should you use DENSE_RANK instead of RANK?

Solution

In this article, we’ll explore these two handy ranking functions in detail. First, we’ll answer the question of what each returns. Then, we’ll walk through a real-world example of when you would choose RANK instead of DENSE_RANK. By the end of this article, you’ll have a simple mental model for consistently knowing when to use the correct function. If I’m interviewing you for a SQL role, expect to be asked this question.

How RANK and DENSE_RANK Work

SQL Server offers several ranking functions, including my favorite, ROW_NUMBER, which assigns a unique row number within a partition. However, coming in a close second and third are RANK and DENSE_RANK. Let’s take a minute to look at what each of them does.

SQL RANK

What better place to find our function definition than from Microsoft? On the Microsoft Learn site, RANK is described as a window function that returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before that row. The basic syntax is shown below. I’m using the VALUES table constructor in all of these examples.

/* MSSQLTips.com */
WITH BaseData
AS (SELECT *
    FROM
    (
        VALUES
            ('Amanda', 98),
            ('Noah', 98),
            ('Sally', 80)
    ) b (FirstName, Score)
   )
SELECT FirstName,
       Score,
       RANK() OVER (ORDER BY Score DESC) AS ScoreRanking
FROM BaseData
ORDER BY ScoreRanking ASC, FirstName ASC;

The results might look like the following table:

FirstNameScoreScoreRanking
Amanda981
Noah981
Sally803

Notice in the example that we skipped the number 2 in our ranking because that’s what RANK does; it skips numbers when there are ties. Now, if for some reason you didn’t want ties at all, you would need to make the ranking order unique, which would cause a tie-breaker. But I want to keep ties, so let’s not do that.

SQL DENSE_RANK

Before we look at the code, let’s review the Microsoft definition of DENSE_RANK: “Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question.” To me, this definition misses the main distinction between the two functions: how they handle ties. In all fairness, Microsoft Learn mentions the tie behavior.

Here is the same structure as the query above, but using DENSE_RANK.

/* MSSQLTips.com */
WITH BaseData
AS (SELECT *
    FROM
    (
        VALUES
            ('Amanda', 98),
            ('Noah', 98),
            ('Sally', 80)
    ) b (FirstName, Score)
   )
SELECT FirstName,
       Score,
       DENSE_RANK() OVER (ORDER BY Score DESC) AS ScoreRanking
FROM BaseData
ORDER BY ScoreRanking ASC, FirstName ASC;

Results:

FirstNameScoreScoreRanking
Amanda981
Noah981
Sally802

The table above shows that DENSE_RANK did not skip number two.

Why Ties Matter

As mentioned above, the main difference between the two functions seems to be how they handle ties. So, what is a tie? The Merriam-Webster dictionary defines a tie as an equality in number (such as votes or scores) or equality in a contest. That seems straightforward because, in our example above, both Noah and Amanda have the same scores, so they receive the same ranking, and therefore, a tie.

We now understand the difference between the two functions, but when does it make sense to use one over the other?

When to use RANK

When I think about skipping a placement, a sport like swimming comes to mind. My oldest son was in the swim club growing up, which meant I attended countless swim meets. What does the placement at a swim meet look like? Let’s create a sample dataset and take a close look.

Swim Meet Results

My son hasn’t competed in years, but the rule I remember is that when there’s a tie in a meet, the two individuals share first place, and there is no second place. The rules are probably somewhere on the www.usaswimming.org website. No doubt, if the information in this article about sports is inaccurate, someone will let me know in the comments. Here’s an example of what the results of a swim meet might look like after we build a small dataset.

/* MSSQLTips.com */
WITH SwimMeetResults
AS (SELECT *
    FROM
    (
        VALUES
            ('Finals', 'Noah', '100m Freestyle', 51.89),
            ('Finals', 'Liam', '100m Freestyle', 52.10),
            ('Finals', 'Emma', '100m Freestyle', 52.10),
            ('Finals', 'Olivia', '100m Freestyle', 52.98),
            ('Finals', 'Ava', '100m Freestyle', 53.45)
    ) AS s (MeetName, SwimmerName, EventName, TimeSeconds)
   )
SELECT MeetName,
       EventName,
       SwimmerName,
       TimeSeconds,
       RANK() OVER (PARTITION BY MeetName, EventName ORDER BY TimeSeconds) AS PositionRANK
FROM SwimMeetResults
ORDER BY MeetName,
         EventName,
         PositionRANK,
         SwimmerName;

Here are the results. In competitive rankings, positions represent placements, not categories. Of course, my son won the meet, and there was no tie for first place.

MeetNameEventNameSwimmerNameTimeSecondsPositionRANK
Finals100m FreestyleNoah51.891
Finals100m FreestyleEmma52.102
Finals100m FreestyleLiam52.102
Finals100m FreestyleOlivia52.984
Finals100m FreestyleAva53.455

When to Use DENSE_RANK

A real-world example of DENSE_RANK is a teacher dividing students into grade groups in a classroom. This isn’t necessarily about rankings, but more about showing which group or level you belong to. This example probably doesn’t relate much to kids nowadays, at least in the United States, but when I was young, things were more competitive, and you knew how you compared to other students. The table below shows how the teacher might view the grading scale.

/* MSSQLTips.com */
WITH BaseData
AS (SELECT *
    FROM
    (
        VALUES
            ('Amanda', 98),
            ('Noah', 98),
            ('Sally', 92),
            ('Billy', 98),
            ('Tommy', 98),
            ('Amy', 80)
    ) b (FirstName, Score)
   )
SELECT FirstName,
       Score,
       DENSE_RANK() OVER (ORDER BY Score DESC) AS GradeLevel
FROM BaseData
ORDER BY GradeLevel ASC,
         FirstName ASC;

Here are the results.

FirstNameScoreGradeLevel
Amanda981
Billy981
Noah981
Tommy981
Sally922
Amy803

To keep the two functions separate in my mind, I think of the word ‘dense,’ meaning trying to pack more numbers in so that no numbers are skipped. If you have trouble remembering them, use mine or come up with your own mental model.

Business Requirements Drive the Choice

Using RANK or DENSE_RANK isn’t a matter of personal preference like choosing between CTEs or derived tables, or even a less controversial topic like which flavor of ice cream you prefer (Chocolate for the win). The decision depends on the business requirements you’re trying to fulfill by writing the query in the first place.

Conclusion

This article answers the question: When should you use DENSE_RANK versus RANK? Both functions assign ranking values to ordered data. RANK creates gaps in the ranking sequence when rows tie, while DENSE_RANK does not. Therefore, you should use RANK when gaps after ties accurately reflect the meaning of the ranking, and use DENSE_RANK when ranks need to stay consecutive. Ultimately, the choice depends on the question you’re trying to answer and the business requirements.

Here’s another way to think about it:

  • RANK() – assigns placement Example: (1, 1, 1, 1, 5)
  • DENSE_RANK() – assigns levels or categories Example: (1, 1, 1, 2, 2)

Now you’ll be ready for that interview!

Next Steps

Related Posts

One comment

  1. Hi
    Thanks For Your Article , I Think Rank Function Assign Number To Person And Dense_Rank Function Assign Number To Score

Leave a Reply

Your email address will not be published. Required fields are marked *