The DENSE_RANK() function
The DENSE_RANK() function in SQL is similar to the RANK() function but with a key difference in how it handles ties. Here’s a detailed overview of the DENSE_RANK() function, including its syntax, usage, and examples.
Overview of DENSE_RANK()
The DENSE_RANK() function is used to assign a unique rank to each row within a partition of a result set, based on the order specified. Unlike RANK(), DENSE_RANK() does not skip ranks when there are ties. This means that if two rows have the same ranking value, they receive the same rank, and the next rank will be the immediate next integer.
Syntax
The basic syntax of the DENSE_RANK() function is:
DENSE_RANK() OVER ( [PARTITION BY partition_column] ORDER BY order_column [ASC|DESC] )
PARTITION BY partition_column: (Optional) Divides the result set into partitions. DENSE_RANK() is applied independently to each partition.
ORDER BY order_column [ASC|DESC]: Specifies the order in which rows are ranked. The default is ascending order (ASC).
Examples of Using DENSE_RANK()
Example 1: Basic Ranking
Assume you have a table sales with columns salesperson_id, sales_amount, and sales_date. To rank the sales based on the sales amount:
SELECT salesperson_id, sales_amount, DENSE_RANK() OVER (ORDER BY sales_amount DESC) AS rank FROM sales;
Explanation:
- DENSE_RANK() OVER (ORDER BY sales_amount DESC) assigns ranks based on the sales amount in descending order.
- Rows with the same sales amount receive the same rank.
- Unlike RANK(), DENSE_RANK() does not skip rank numbers after ties. If two sales amounts are tied for rank 1, the next rank will be 2.
Example 2: Ranking with Partitioning
To rank the sales within each salesperson:
SELECT salesperson_id, sales_amount, DENSE_RANK() OVER (PARTITION BY salesperson_id ORDER BY sales_amount DESC) AS rank FROM sales;
Explanation:
- PARTITION BY salesperson_id divides the data by salesperson.
- DENSE_RANK() ranks the sales amounts within each salesperson partition.
- Sales amounts tied within the same salesperson receive the same rank, with no gaps in the ranking sequence.
Example 3: Ranking with Ties
Consider a table students with columns name, score, and class. To rank students by their scores within each class:
SELECT name, score, class, DENSE_RANK() OVER (PARTITION BY class ORDER BY score DESC) AS rank FROM students;
Explanation:
- Students are partitioned by class.
- DENSE_RANK() ranks students by their score within each class.
- Students with the same score receive the same rank, with subsequent ranks continuing without gaps.
Key Points
- Handling Ties: DENSE_RANK() assigns the same rank to rows with the same values in the ordering column(s). Unlike RANK(), it does not skip ranks. For example, if two rows are ranked 1, the next row will be ranked 2.
- Rank Value Progression: Ranks are continuous, with no gaps between the ranks even if there are ties. For instance, if two items are ranked 1, the next item is ranked 2.
- Performance: Calculating dense ranks can be resource-intensive on large datasets or complex queries. Make sure to optimize your queries for better performance if needed.
- Comparison with RANK(): DENSE_RANK() is often compared to RANK(). RANK() skips rank values after ties, which can be useful in different analytical scenarios. DENSE_RANK() ensures that ranks are sequential without gaps.
Use Cases
Employee Performance Evaluation
To evaluate and rank employees based on their sales performance, ensuring there are no gaps in ranks:
SELECT employee_id, sales, DENSE_RANK() OVER (ORDER BY sales DESC) AS rank FROM employee_performance;
Sports Competition Results
To rank athletes based on their scores without gaps in ranking, even if there are ties:
SELECT athlete_id, score, DENSE_RANK() OVER (ORDER BY score DESC) AS rank FROM competition_results;
Project Budget Prioritization
To rank projects by their budget, ensuring continuous ranks even with tied budgets:
SELECT project_id, budget, DENSE_RANK() OVER (ORDER BY budget DESC) AS priority FROM projects;
Conclusion
The DENSE_RANK() function is an essential tool for ranking data, particularly when you need to avoid gaps in ranking values, even when there are ties. Understanding how DENSE_RANK() handles ties and how it differs from RANK() can help you choose the right function for your specific analytical needs.