rank vs dense_rank in SQL

SQL is a powerful tool for managing and analyzing data. When it comes to sorting and ranking data in SQL, there are various ranking functions available. Two commonly used ranking functions in SQL are RANK and DENSE_RANK. These functions allow us to assign a rank to each row based on a particular criterion, such as a specific column value. In this blog post, we will explore the difference between RANK and DENSE_RANK in SQL and how to use them with examples.

What is Rank in SQL?

RANK is a function in SQL that assigns a rank to each row within a result set based on the values in one or more columns. The rank assigned to each row depends on its position in the sorted result set. Rows with the same value in the ordering column are assigned the same rank, with the next rank skipped.

Syntax: The syntax for RANK function in SQL is as follows:

RANK() OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ... )

Example: Consider a table “Employee” with columns “Name”, “Department” and “Salary”. We want to find the rank of each employee based on their salary.

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

This query will sort the employees based on their salary in descending order and assign ranks based on their position in the sorted result set.

What is DenseRank in SQL?

DENSE_RANK is another ranking function in SQL that assigns a rank to each row based on the values in one or more columns. The difference between RANK and DENSE_RANK is that DENSE_RANK does not skip any rank, even if multiple rows have the same rank.

Syntax: The syntax for DENSE_RANK function in SQL is as follows:

DENSE_RANK() OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ... )

Example: Using the same “Employee” table, we can find the dense rank of each employee based on their salary as follows:

SELECT Name, Department, Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) as Dense_Rank
FROM Employee;

This query will sort the employees based on their salary in descending order and assign dense ranks based on their position in the sorted result set.

Difference between Rank and DenseRank in SQL:

The main difference between RANK and DENSE_RANK is in the way they assign ranks to the rows. RANK skips the next rank if multiple rows have the same rank, while DENSE_RANK assigns the same rank to all rows with the same value.

For example, consider the following table:

Let’s say we want to rank the salespersons based on their total sales. We can use the following query:

SELECT salesperson, SUM(sales) as total_sales, 
       RANK() OVER (ORDER BY SUM(sales) DESC) as rank,
       DENSE_RANK() OVER (ORDER BY SUM(sales) DESC) as dense_rank
FROM sales_data
GROUP BY salesperson;

The output of this query would be:

Here, we have used the RANK() and DENSE_RANK() functions to assign a rank to each salesperson based on their total sales. As we can see, both RANK() and DENSE_RANK() have ranked Sarah as #1 since she has the highest total sales. However, there is a difference in the rank assigned to John and Mike.

With RANK(), the next rank is skipped if there is a tie. So, since John and Mike have the same total sales of 1000, they are both assigned a rank of 2 and the next rank (3) is skipped.

On the other hand, with DENSE_RANK(), the next rank is not skipped if there is a tie. So, even though John and Mike have the same total sales, they are both assigned a rank of 3, and the next rank (4) is skipped.

Therefore, the main difference between RANK() and DENSE_RANK() is that RANK() assigns a unique rank to each row, but skips the next rank if there is a tie, while DENSE_RANK() assigns a unique rank to each row but does not skip the next rank if there is a tie.