Introduction

RANK() in MySQL is a window function that assigns an ordered position to each row within a result set or partition. It is especially useful for leaderboards, top-per-group reports, and any query where ties should share the same position instead of receiving arbitrary row numbers.

Basic RANK() Syntax

RANK() is available in MySQL 8.0 and later as part of the window function feature set. The basic shape is:

1RANK() OVER (
2    PARTITION BY department
3    ORDER BY salary DESC
4)

PARTITION BY is optional. It splits the result into separate groups before ranking. ORDER BY is what defines the ranking itself.

Example: Ranking Employees by Salary

Suppose you have an employees table:

1SELECT
2    employee_name,
3    department,
4    salary,
5    RANK() OVER (
6        PARTITION BY department
7        ORDER BY salary DESC
8    ) AS salary_rank
9FROM employees;

If two employees in the same department have the same salary, they receive the same rank. The next rank then skips ahead.

For example, if salaries rank as 100, 90, 90, 80, the ranks will be 1, 2, 2, 4.

That gap is the defining behavior of RANK().

RANK() vs DENSE_RANK() vs ROW_NUMBER()

These three functions are often confused:

• 'RANK() gives ties the same rank and leaves gaps afterward'
• 'DENSE_RANK() gives ties the same rank but does not leave gaps'
• 'ROW_NUMBER() gives every row a unique sequence number'

A comparison query makes the difference obvious:

1SELECT
2    employee_name,
3    department,
4    salary,
5    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_value,
6    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank_value,
7    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_number_value
8FROM employees;

Choose RANK() when ties should visibly affect later positions. Choose DENSE_RANK() when you want tied rows grouped together but still want consecutive numbering.

Ranking Without Partitions

If you omit PARTITION BY, MySQL ranks across the entire result set:

1SELECT
2    employee_name,
3    salary,
4    RANK() OVER (ORDER BY salary DESC) AS overall_rank
5FROM employees;

This is useful for a global leaderboard or any whole-table ordering problem.

Filtering Ranked Results

A common pattern is to rank in a subquery or common table expression and then filter the ranked rows:

1WITH ranked_employees AS (
2    SELECT
3        employee_name,
4        department,
5        salary,
6        RANK() OVER (
7            PARTITION BY department
8            ORDER BY salary DESC
9        ) AS salary_rank
10    FROM employees
11)
12SELECT *
13FROM ranked_employees
14WHERE salary_rank <= 3;

That returns the top three salary ranks per department, including ties when they fall within the requested rank range.

This pattern is often easier to maintain than trying to simulate ranking with self-joins or user-defined variables, especially now that window functions are built into modern MySQL.

Common Pitfalls

The biggest mistake is expecting RANK() to produce consecutive numbers after ties. That is what DENSE_RANK() does, not RANK().

Another common issue is forgetting that window functions require a meaningful ORDER BY. If the ordering column is ambiguous or not aligned with the business rule, the assigned rank will not match the intended definition.

It is also easy to forget the MySQL version requirement. RANK() depends on window function support, so it is not available in older MySQL versions that predate 8.0.

Summary

• 'RANK() assigns positions within an ordered result set or partition.'
• In MySQL, it is used with OVER (...) and requires an ORDER BY inside the window definition.
• Tied rows receive the same rank, and later ranks skip numbers.
• Use DENSE_RANK() if you want ties without gaps.
• 'PARTITION BY lets you rank independently inside groups such as departments or categories.'