Understanding DENSE_RANK in SQL

Ayushi Trivedi 13 Jun, 2024
4 min read

Introduction

When working with databases and analyzing data, ranking records is very important for organizing information based on certain conditions. One ranking function called `DENSE_RANK()` is useful because it assigns ranks to rows without leaving any empty spaces or gaps. This guide explains what `DENSE_RANK()` is, how it operates, and when to use it effectively in SQL.

Overview

  • Understand the basic function and use of SQL’s DENSE_RANK() function.
  • Use SQL queries using the DENSE_RANK() function to rank rows in a dataset according to predetermined standards.
  • Manage scenarios where multiple records share the same ranking value and ensure consecutive ranking without gaps using DENSE_RANK().
  • Implement ranking for statistical analysis, such as calculating percentiles and quartiles, ensuring a continuous sequence of ranks.
  • Use DENSE_RANK() in conjunction with other SQL functions to produce detailed and insightful reports.
DENSE_RANK in SQL

What is DENSE_RANK()?

The DENSE_RANK() function in SQL assigns a rank number to each row within a section or partition of the results. It works differently than the RANK() function, which may skip rank numbers when there are ties or identical values. With DENSE_RANK(), the ranks are assigned one after the other continuously, with no gaps. So if two rows have the same value and are tied for a rank, the very next rank number is used right after, without skipping any numbers.

SQL

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

  • PARTITION BY: This optional clause divides the result set into partitions. The `DENSE_RANK()` function is applied to each partition separately. If omitted, the entire result set is treated as a single partition.
  • ORDER BY: This clause specifies the order in which the rows are ranked.

How Does DENSE_RANK() Work?

To understand how `DENSE_RANK()` works, let’s consider an example. Suppose you have a table named `sales` with the following data:

| Product | Sales |

|---------|-------|

| A       | 100   |

| B       | 200   |

| C       | 200   |

| D       | 300   |

Using the `DENSE_RANK()` function to rank these products by their sales in descending order would look like this:

SQL

SELECT Product, Sales,
    DENSE_RANK() OVER (ORDER BY Sales DESC) AS Rank
FROM sales;

The result would be:

| Product | Sales | Rank |

|---------|-------|------|

| D       | 300   | 1    |

| B       | 200   | 2    |

| C       | 200   | 2    |

| A       | 100   | 3    |

As shown, products B and C have the same sales amount and are both ranked 2nd. The next rank is 3rd, without any gaps.

Practical Applications of DENSE_RANK()

`DENSE_RANK()` is particularly useful in various scenarios, such as:

  • Identifying Top Performers: In business settings, you might need to identify top-performing salespeople, products, or departments. `DENSE_RANK()` can help you rank these entities without leaving gaps, providing a clear view of performance.
  • Handling Ties: When multiple records share the same value, `DENSE_RANK()` ensures that they receive the same rank, and the next rank follows consecutively. This is useful in competitions or any scenario where tied results need to be handled gracefully.
  • Pagination: In web applications, `DENSE_RANK()` can be used to implement pagination by ranking results and then displaying them in manageable chunks.
  • Statistical Analysis: `DENSE_RANK()` is essential for various analytical functions, such as calculating percentiles, quartiles, and other statistical measures that require a continuous sequence of ranks.

Examples of DENSE_RANK() in Action

Let’s explore a few examples to illustrate the use of `DENSE_RANK()` in different contexts.

Example 1: Ranking Products by Price

Consider a `products` table with columns `product_id`, `product_name`, and `price`. To rank products by their price in descending order:

SQL

SELECT product_id, product_name, price,
    DENSE_RANK() OVER (ORDER BY price DESC) AS price_rank
FROM products;

This query will assign ranks to products based on their price, with the highest-priced product ranked first.

Example 2: Ranking Employees by Department and Salary

Suppose you have an `employees` table with columns `employee_id`, `department_id`, and `salary`. To rank employees within each department by their salary:

SQL

SELECT employee_id, department_id, salary,
    DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM employees;

This query will rank employees within each department separately, ensuring that the ranking is based on their salary.

Differences Between RANK() and DENSE_RANK()

While both `RANK()` and `DENSE_RANK()` are used to rank rows based on specified criteria, they differ in handling ties:

  • RANK(): Leaves gaps in the ranking sequence when there are ties. For example, if two rows tie for the first rank, the next rank will be 3.
  • DENSE_RANK(): Does not leave gaps. The next rank will immediately follow the previous rank, even if there are ties.

Example:

Given the same `sales` table, using `RANK()` instead of `DENSE_RANK()`:

SQL

SELECT Product, Sales,
    RANK() OVER (ORDER BY Sales DESC) AS Rank
FROM sales;

The result would be:

| Product | Sales | Rank |

|---------|-------|------|

| D       | 300   | 1    |

| B       | 200   | 2    |

| C       | 200   | 2    |

| A       | 100   | 4    |

Notice the gap between ranks 2 and 4.

Conclusion

The `DENSE_RANK()` function is a useful tool in SQL for giving rank numbers to rows in a dataset based on certain conditions. The ranks will be one after the other, without any gaps, even if some rows have the same value and are tied. Understanding and using `DENSE_RANK()` can improve your ability to analyze data effectively and present it clearly. Whether you need to identify top performers, deal with ties or identical values, or do statistical analysis, `DENSE_RANK()` provides a solid way to rank data without leaving any empty spaces in the ranking sequence.

Frequently Asked Questions

Q1. What is the DENSE_RANK() function in SQL?

A. When there are ties in the ranking sequence, the SQL DENSE_RANK() method prevents gaps by giving a rank to each row inside a partition of the result set.

Q2. How does DENSE_RANK() differ from RANK()?

A. Whereas RANK() inserts gaps in the ranking sequence after tied values, DENSE_RANK() assigns the same rank to tied values without any gaps.

Q3. Can I use DENSE_RANK() with the PARTITION BY clause?

A. Yes, you can rank rows within different partitions of a result set using DENSE_RANK() and the PARTITION BY clause. This enables distinct ranking sequences according to the designated order within every partition.

Ayushi Trivedi 13 Jun, 2024

Frequently Asked Questions

Lorem ipsum dolor sit amet, consectetur adipiscing elit,

Responses From Readers

Clear