SQL - DENSE_RANK() Function | Application Architect

Key Insights

DENSE_RANK() assigns consecutive ranks without gaps when ties occur, unlike RANK() which leaves gaps equal to the number of tied rows
The function requires an ORDER BY clause within the OVER() specification and supports PARTITION BY for calculating ranks within logical groups
DENSE_RANK() is ideal for scenarios requiring top-N queries, competitive rankings, and eliminating duplicate records while preserving rank continuity

Understanding DENSE_RANK() Fundamentals

DENSE_RANK() is a window function that assigns a rank to each row within a partition of a result set. The key characteristic that distinguishes it from other ranking functions is its handling of ties: when multiple rows share the same values, they receive the same rank, and the next rank is consecutive without gaps.

SELECT 
    employee_name,
    department,
    salary,
    DENSE_RANK() OVER (ORDER BY salary DESC) as salary_rank
FROM employees;

This query ranks employees by salary. If two employees earn $100,000, both receive rank 1, and the next highest salary receives rank 2 (not rank 3).

DENSE_RANK() vs RANK() vs ROW_NUMBER()

Understanding the differences between ranking functions is critical for correct implementation:

WITH sales_data AS (
    SELECT 'John' as rep, 5000 as amount UNION ALL
    SELECT 'Jane', 5000 UNION ALL
    SELECT 'Bob', 4500 UNION ALL
    SELECT 'Alice', 4500 UNION ALL
    SELECT 'Tom', 4000
)
SELECT 
    rep,
    amount,
    ROW_NUMBER() OVER (ORDER BY amount DESC) as row_num,
    RANK() OVER (ORDER BY amount DESC) as rank,
    DENSE_RANK() OVER (ORDER BY amount DESC) as dense_rank
FROM sales_data;

Results:

rep   | amount | row_num | rank | dense_rank
------|--------|---------|------|------------
John  | 5000   | 1       | 1    | 1
Jane  | 5000   | 2       | 1    | 1
Bob   | 4500   | 3       | 3    | 2
Alice | 4500   | 4       | 3    | 2
Tom   | 4000   | 5       | 5    | 3

ROW_NUMBER() assigns unique sequential numbers regardless of ties. RANK() creates gaps after ties. DENSE_RANK() maintains consecutive numbering.

Partitioning with DENSE_RANK()

PARTITION BY divides the result set into logical groups, calculating ranks independently within each partition:

SELECT 
    product_name,
    category,
    revenue,
    DENSE_RANK() OVER (
        PARTITION BY category 
        ORDER BY revenue DESC
    ) as category_rank
FROM product_sales
WHERE sale_year = 2024;

This ranks products within each category separately. The top product in Electronics gets rank 1, and the top product in Clothing also gets rank 1.

Complex partitioning example with multiple columns:

SELECT 
    student_name,
    subject,
    grade_level,
    test_score,
    DENSE_RANK() OVER (
        PARTITION BY grade_level, subject 
        ORDER BY test_score DESC
    ) as subject_rank
FROM test_results;

This creates separate rankings for each combination of grade level and subject.

Top-N Queries with DENSE_RANK()

DENSE_RANK() excels at finding top performers while handling ties appropriately:

WITH ranked_products AS (
    SELECT 
        product_id,
        product_name,
        total_sales,
        DENSE_RANK() OVER (ORDER BY total_sales DESC) as sales_rank
    FROM product_metrics
)
SELECT 
    product_id,
    product_name,
    total_sales,
    sales_rank
FROM ranked_products
WHERE sales_rank <= 5;

This returns the top 5 products by sales. If multiple products tie for 5th place, all are included.

Top-N per category:

WITH category_rankings AS (
    SELECT 
        category,
        product_name,
        units_sold,
        DENSE_RANK() OVER (
            PARTITION BY category 
            ORDER BY units_sold DESC
        ) as rank_in_category
    FROM inventory
)
SELECT 
    category,
    product_name,
    units_sold
FROM category_rankings
WHERE rank_in_category <= 3
ORDER BY category, rank_in_category;

Handling NULL Values

DENSE_RANK() treats NULL values according to the database’s sorting behavior. Most databases sort NULLs first or last:

-- NULLs appear last (default in most databases)
SELECT 
    customer_name,
    last_purchase_date,
    DENSE_RANK() OVER (
        ORDER BY last_purchase_date DESC NULLS LAST
    ) as recency_rank
FROM customers;

-- NULLs appear first
SELECT 
    customer_name,
    last_purchase_date,
    DENSE_RANK() OVER (
        ORDER BY last_purchase_date DESC NULLS FIRST
    ) as recency_rank
FROM customers;

Deduplication with DENSE_RANK()

DENSE_RANK() provides an effective method for removing duplicates while preserving specific records:

WITH ranked_records AS (
    SELECT 
        customer_id,
        order_date,
        order_amount,
        DENSE_RANK() OVER (
            PARTITION BY customer_id 
            ORDER BY order_date DESC
        ) as order_sequence
    FROM orders
)
SELECT 
    customer_id,
    order_date,
    order_amount
FROM ranked_records
WHERE order_sequence = 1;

This retrieves the most recent order for each customer. Unlike ROW_NUMBER(), if a customer has multiple orders on the same date, all are returned.

Complex Ranking Scenarios

Multi-column sorting with DENSE_RANK():

SELECT 
    athlete_name,
    sport,
    gold_medals,
    silver_medals,
    bronze_medals,
    DENSE_RANK() OVER (
        PARTITION BY sport
        ORDER BY 
            gold_medals DESC,
            silver_medals DESC,
            bronze_medals DESC
    ) as overall_rank
FROM olympic_results;

Conditional ranking using CASE expressions:

SELECT 
    employee_id,
    department,
    performance_score,
    years_employed,
    DENSE_RANK() OVER (
        PARTITION BY department
        ORDER BY 
            CASE 
                WHEN years_employed >= 5 THEN performance_score * 1.2
                ELSE performance_score
            END DESC
    ) as adjusted_rank
FROM employee_reviews;

Performance Considerations

DENSE_RANK() operations can be resource-intensive on large datasets. Optimize with appropriate indexes:

-- Create index on columns used in PARTITION BY and ORDER BY
CREATE INDEX idx_sales_category_revenue 
ON product_sales(category, revenue DESC);

-- Query benefits from index
SELECT 
    product_name,
    category,
    revenue,
    DENSE_RANK() OVER (
        PARTITION BY category 
        ORDER BY revenue DESC
    ) as rank
FROM product_sales;

Limit the dataset before applying DENSE_RANK():

WITH filtered_data AS (
    SELECT *
    FROM transactions
    WHERE transaction_date >= '2024-01-01'
        AND status = 'completed'
)
SELECT 
    customer_id,
    transaction_amount,
    DENSE_RANK() OVER (
        ORDER BY transaction_amount DESC
    ) as amount_rank
FROM filtered_data;

Combining DENSE_RANK() with Aggregates

Calculate ranks based on aggregated values:

WITH monthly_totals AS (
    SELECT 
        sales_rep_id,
        DATE_TRUNC('month', sale_date) as month,
        SUM(sale_amount) as monthly_total
    FROM sales
    GROUP BY sales_rep_id, DATE_TRUNC('month', sale_date)
)
SELECT 
    sales_rep_id,
    month,
    monthly_total,
    DENSE_RANK() OVER (
        PARTITION BY month 
        ORDER BY monthly_total DESC
    ) as monthly_rank
FROM monthly_totals;

This pattern is essential for time-series ranking and comparative analysis across periods. DENSE_RANK() ensures that representatives with identical sales figures receive the same recognition, while maintaining a logical progression of ranks for reporting and bonus calculations.