How to Use DENSE_RANK in MySQL

Key Insights

• DENSE_RANK assigns ranks without gaps when ties occur, making it ideal for leaderboards and competitive rankings where every rank position matters
• Unlike RANK, which skips numbers after ties, DENSE_RANK ensures consecutive ranking (1, 2, 2, 3 instead of 1, 2, 2, 4), while ROW_NUMBER always assigns unique values
• Combining DENSE_RANK with PARTITION BY enables sophisticated per-group rankings in a single query, eliminating the need for complex subqueries or self-joins

Introduction to DENSE_RANK

The DENSE_RANK() window function arrived in MySQL 8.0 as part of the database’s long-awaited window function support. It solves a common problem: assigning ranks to rows based on specific criteria while handling ties gracefully and maintaining consecutive rank numbers.

Before MySQL 8.0, developers resorted to user-defined variables or complex subqueries to achieve ranking functionality. DENSE_RANK eliminates this complexity by providing a declarative, SQL-standard approach to ranking data. The function assigns the same rank to rows with identical values in the ORDER BY clause, but crucially, it doesn’t skip any rank numbers in the sequence.

The primary use case is straightforward: whenever you need to rank data and ties should share the same rank without creating gaps in your ranking sequence. Think leaderboards, product rankings, or academic grade distributions where every rank position has meaning.

DENSE_RANK vs RANK vs ROW_NUMBER

Understanding the differences between these three ranking functions is critical for choosing the right tool. Let’s examine them side by side with employee salary data:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

INSERT INTO employees VALUES
(1, 'Alice', 'Engineering', 95000),
(2, 'Bob', 'Engineering', 95000),
(3, 'Carol', 'Engineering', 87000),
(4, 'David', 'Engineering', 87000),
(5, 'Eve', 'Engineering', 82000);

SELECT 
    name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) as row_num,
    RANK() OVER (ORDER BY salary DESC) as rank_num,
    DENSE_RANK() OVER (ORDER BY salary DESC) as dense_rank_num
FROM employees
WHERE department = 'Engineering'
ORDER BY salary DESC;

Results:

name   | salary  | row_num | rank_num | dense_rank_num
-------|---------|---------|----------|---------------
Alice  | 95000   | 1       | 1        | 1
Bob    | 95000   | 2       | 1        | 1
Carol  | 87000   | 3       | 3        | 2
David  | 87000   | 4       | 3        | 2
Eve    | 82000   | 5       | 5        | 3

ROW_NUMBER() assigns unique sequential integers regardless of ties. Alice and Bob both earn $95,000, but they receive different row numbers (1 and 2).

RANK() assigns the same rank to ties but skips subsequent ranks. Alice and Bob both get rank 1, but the next rank jumps to 3, skipping 2 entirely. This creates gaps in the ranking sequence.

DENSE_RANK() assigns the same rank to ties and continues with the next consecutive integer. Alice and Bob share rank 1, and the next rank is 2—no gaps. This is the key distinction and why DENSE_RANK is preferred for most ranking scenarios.

Basic DENSE_RANK Syntax and Usage

The fundamental syntax is simple:

DENSE_RANK() OVER (ORDER BY column [ASC|DESC])

The OVER clause is mandatory and defines the window specification. At minimum, you need an ORDER BY within the OVER clause to establish the ranking criteria.

Let’s rank products by price:

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    category VARCHAR(50),
    price DECIMAL(10, 2)
);

INSERT INTO products VALUES
(1, 'Laptop Pro', 'Electronics', 1299.99),
(2, 'Laptop Air', 'Electronics', 999.99),
(3, 'Laptop Basic', 'Electronics', 699.99),
(4, 'Laptop Standard', 'Electronics', 699.99),
(5, 'Mouse Wireless', 'Accessories', 29.99);

SELECT 
    name,
    price,
    DENSE_RANK() OVER (ORDER BY price DESC) as price_rank
FROM products
ORDER BY price DESC;

Results:

name            | price   | price_rank
----------------|---------|------------
Laptop Pro      | 1299.99 | 1
Laptop Air      | 999.99  | 2
Laptop Basic    | 699.99  | 3
Laptop Standard | 699.99  | 3
Mouse Wireless  | 29.99   | 4

Both laptops at $699.99 receive rank 3, and the next product gets rank 4—no gaps. This consecutive numbering is what makes DENSE_RANK particularly useful for display purposes where you want to show “Top 3” items and actually mean three distinct price points, not three rows.

Using PARTITION BY with DENSE_RANK

PARTITION BY transforms DENSE_RANK from useful to powerful. It divides your result set into groups and applies ranking independently within each group. This eliminates the need for multiple queries or complex subqueries.

Here’s how to rank employees by salary within each department:

INSERT INTO employees VALUES
(6, 'Frank', 'Sales', 78000),
(7, 'Grace', 'Sales', 78000),
(8, 'Henry', 'Sales', 71000),
(9, 'Iris', 'Marketing', 82000),
(10, 'Jack', 'Marketing', 76000);

SELECT 
    department,
    name,
    salary,
    DENSE_RANK() OVER (
        PARTITION BY department 
        ORDER BY salary DESC
    ) as dept_rank
FROM employees
ORDER BY department, dept_rank;

Results:

department  | name  | salary | dept_rank
------------|-------|--------|----------
Engineering | Alice | 95000  | 1
Engineering | Bob   | 95000  | 1
Engineering | Carol | 87000  | 2
Engineering | David | 87000  | 2
Engineering | Eve   | 82000  | 3
Marketing   | Iris  | 82000  | 1
Marketing   | Jack  | 76000  | 2
Sales       | Frank | 78000  | 1
Sales       | Grace | 78000  | 1
Sales       | Henry | 71000  | 2

The ranking resets for each department. This pattern is essential for per-category analysis and is far more efficient than running separate queries for each department.

Practical Use Cases

Top-N Products Per Category

Finding the top 3 products in each category is a common requirement. Combine DENSE_RANK with a subquery or CTE:

WITH RankedProducts AS (
    SELECT 
        category,
        name,
        price,
        DENSE_RANK() OVER (
            PARTITION BY category 
            ORDER BY price DESC
        ) as price_rank
    FROM products
)
SELECT category, name, price, price_rank
FROM RankedProducts
WHERE price_rank <= 3
ORDER BY category, price_rank;

This approach scales beautifully. Whether you have 5 categories or 500, you’re executing a single query with consistent performance characteristics.

Student Grade Rankings

Academic rankings benefit from DENSE_RANK because tied scores should receive the same rank without penalizing subsequent students:

CREATE TABLE student_scores (
    student_id INT,
    student_name VARCHAR(100),
    class VARCHAR(50),
    score INT
);

INSERT INTO student_scores VALUES
(1, 'Amy', 'Math 101', 95),
(2, 'Ben', 'Math 101', 95),
(3, 'Chris', 'Math 101', 88),
(4, 'Dana', 'Math 101', 85),
(5, 'Emma', 'Physics 201', 92),
(6, 'Finn', 'Physics 201', 90);

SELECT 
    class,
    student_name,
    score,
    DENSE_RANK() OVER (
        PARTITION BY class 
        ORDER BY score DESC
    ) as class_rank
FROM student_scores
ORDER BY class, class_rank;

Amy and Ben both scored 95 and share rank 1. Chris, despite being third in the list, receives rank 2—not rank 3. This is fair and intuitive for grade distributions.

Common Pitfalls and Performance Considerations

NULL Handling

NULLs are sorted according to your ORDER BY direction. By default in MySQL, NULLs sort first in ascending order and last in descending order:

CREATE TABLE test_nulls (
    id INT,
    value INT
);

INSERT INTO test_nulls VALUES (1, 100), (2, NULL), (3, 90), (4, NULL);

SELECT 
    id,
    value,
    DENSE_RANK() OVER (ORDER BY value DESC) as rank_desc,
    DENSE_RANK() OVER (ORDER BY value ASC) as rank_asc
FROM test_nulls;

Results show NULLs ranked last when descending, first when ascending. All NULLs receive the same rank. Be explicit about NULL handling in your ORDER BY if this matters for your use case.

Filtering After Ranking

You cannot use window functions directly in WHERE clauses. Use a subquery or CTE:

-- This FAILS
SELECT name, salary, 
    DENSE_RANK() OVER (ORDER BY salary DESC) as rank
FROM employees
WHERE DENSE_RANK() OVER (ORDER BY salary DESC) <= 3;

-- This WORKS
SELECT name, salary, rank
FROM (
    SELECT name, salary,
        DENSE_RANK() OVER (ORDER BY salary DESC) as rank
    FROM employees
) ranked
WHERE rank <= 3;

Performance Considerations

Window functions require sorting and potentially buffering data. Ensure your ORDER BY columns are indexed when possible. However, window functions typically perform better than equivalent correlated subqueries or self-joins.

For very large datasets, consider:

• Limiting your result set before applying DENSE_RANK
• Using appropriate indexes on PARTITION BY and ORDER BY columns
• Monitoring memory usage, as window functions may require temporary disk storage

Conclusion

DENSE_RANK is the go-to ranking function when you need consecutive rank numbers without gaps. Choose it over RANK when every rank position has semantic meaning—leaderboards, academic rankings, and top-N analyses all benefit from its gap-free behavior.

Use ROW_NUMBER when you need strictly unique identifiers, RANK when gaps after ties are acceptable or desired, and DENSE_RANK for everything else. Combined with PARTITION BY, DENSE_RANK enables sophisticated per-group analytics in single queries that would otherwise require complex procedural code.

The function requires MySQL 8.0 or later, so verify your version before deployment. Once available, it becomes an indispensable tool for ranking and analytics queries.