rank vs dense_rank

Nve Author

2 min read

·

03-04-2025

1

0

Share

Ranking functions in SQL are crucial for assigning ranks to rows within a result set based on the values of one or more columns. Two of the most commonly used ranking functions are RANK() and DENSE_RANK(). While both assign ranks, they differ significantly in how they handle ties. Understanding this difference is key to choosing the right function for your specific needs. This article will explore these differences, using examples drawn from Stack Overflow discussions to illustrate practical applications.

Understanding RANK()

The RANK() function assigns ranks based on the order of rows. Crucially, it assigns the same rank to rows with equal values in the ordering column(s), then skips the next rank. This creates gaps in the ranking sequence.

Example (based on implicit understanding from various Stack Overflow posts regarding rank functions):

Let's say we have a table of employee salaries:

Using RANK() to rank employees by salary:

SELECT Employee, Salary, RANK() OVER (ORDER BY Salary DESC) as Rank
FROM Employees;

Result:

Notice how the three employees with the highest salary (80000) all share rank 1, and the next rank is 4, skipping 2 and 3. This gap is characteristic of RANK().

Understanding DENSE_RANK()

DENSE_RANK() also ranks rows based on the order of values, but unlike RANK(), it assigns consecutive ranks without gaps, even when there are ties. If multiple rows have the same value, they all receive the same rank, and the next rank is the immediately following integer.

Using the same employee salary data, let's apply DENSE_RANK():

SELECT Employee, Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) as DenseRank
FROM Employees;

Result:

Here, all employees with the same salary receive the same rank, and the ranks are consecutive – no gaps. This is the key difference from RANK().

When to Use Which Function?

The choice between RANK() and DENSE_RANK() depends on the specific requirements of your application.

• Use RANK() when: You need to explicitly show the number of ties at a particular rank, even if it creates gaps in the ranking sequence. This is useful when you want to highlight the number of individuals tied for a specific position. This is often relevant in scenarios like leaderboards where indicating ties is important.

• Use DENSE_RANK() when: You need a continuous ranking sequence without gaps, even if there are ties. This is useful when the exact rank number is less important than the relative position compared to other rows. For instance, in awarding medals (gold, silver, bronze), you may prefer to use DENSE_RANK().

Beyond the Basics: Partitioning and NULL Handling

Both RANK() and DENSE_RANK() support partitioning, allowing you to perform ranking within groups defined by other columns. Additionally, both functions handle NULL values differently. The exact behavior depends on the specific database system but generally NULLs are treated either as the lowest or highest values depending on the ORDER BY clause. Consult your database documentation for precise details. This is a common question on Stack Overflow and often necessitates a deeper dive into specific database implementations.

This article provides a clear and concise explanation of the difference between RANK() and DENSE_RANK(), enhanced with illustrative examples. Remember to consult your database's specific documentation for detailed information about the behavior of these functions, especially regarding NULL handling and advanced features like partitioning.