How to Use RANK in MySQL
MySQL 8.0 introduced window functions, fundamentally changing how we approach analytical queries. RANK is one of the most useful window functions, assigning rankings to rows based on specified...
Key Insights
- RANK assigns sequential rankings with gaps after ties, making it ideal for competitive leaderboards and performance analysis where tied positions should skip subsequent ranks
- Window functions like RANK can’t be filtered directly in WHERE clauses—use CTEs or subqueries to filter ranked results effectively
- PARTITION BY restarts ranking for each group, enabling powerful “top N per category” queries that would otherwise require complex self-joins or correlated subqueries
Introduction to Window Functions and RANK
MySQL 8.0 introduced window functions, fundamentally changing how we approach analytical queries. RANK is one of the most useful window functions, assigning rankings to rows based on specified criteria while handling ties intelligently.
The key difference between the three ranking functions lies in how they handle ties:
- RANK: Assigns the same rank to ties, then skips the next positions (1, 2, 2, 4, 5)
- DENSE_RANK: Assigns the same rank to ties without gaps (1, 2, 2, 3, 4)
- ROW_NUMBER: Assigns unique sequential numbers regardless of ties (1, 2, 3, 4, 5)
SELECT
name,
score,
RANK() OVER (ORDER BY score DESC) as rank_position,
DENSE_RANK() OVER (ORDER BY score DESC) as dense_rank_position,
ROW_NUMBER() OVER (ORDER BY score DESC) as row_number_position
FROM test_scores;
-- Results:
-- name score rank_position dense_rank_position row_number_position
-- Alice 95 1 1 1
-- Bob 92 2 2 2
-- Carol 92 2 2 3
-- David 88 4 3 4
-- Eve 85 5 4 5
Notice how RANK skips position 3 after the tie at position 2. This behavior mirrors competitive rankings where two athletes sharing second place means no one gets third place.
Basic RANK Syntax and Usage
The RANK function requires an ORDER BY clause within the OVER specification. Without it, MySQL will throw an error—ranking without ordering criteria makes no sense.
The basic syntax is straightforward:
RANK() OVER (ORDER BY column_name [ASC|DESC])
Let’s apply this to a practical employee salary ranking scenario:
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
name VARCHAR(100),
department VARCHAR(50),
salary DECIMAL(10, 2)
);
INSERT INTO employees VALUES
(1, 'Sarah Chen', 'Engineering', 125000),
(2, 'Mike Johnson', 'Engineering', 115000),
(3, 'Lisa Wang', 'Sales', 95000),
(4, 'Tom Brady', 'Sales', 105000),
(5, 'Anna Smith', 'Engineering', 115000),
(6, 'Carlos Rodriguez', 'Marketing', 88000);
SELECT
name,
department,
salary,
RANK() OVER (ORDER BY salary DESC) as salary_rank
FROM employees
ORDER BY salary_rank;
-- Results:
-- name department salary salary_rank
-- Sarah Chen Engineering 125000.00 1
-- Tom Brady Sales 105000.00 2
-- Mike Johnson Engineering 115000.00 3
-- Anna Smith Engineering 115000.00 3
-- Lisa Wang Sales 95000.00 5
-- Carlos Rodriguez Marketing 88000.00 6
Mike and Anna both earn $115,000, so they share rank 3. The next employee, Lisa, gets rank 5—not rank 4. This gap is RANK’s defining characteristic.
RANK with PARTITION BY
PARTITION BY transforms RANK from useful to indispensable. It divides your result set into groups, restarting the ranking for each partition. This enables “top N per category” queries without complex joins.
The syntax adds PARTITION BY before ORDER BY:
RANK() OVER (PARTITION BY partition_column ORDER BY order_column)
Here’s a real-world example ranking employees by salary within each department:
SELECT
name,
department,
salary,
RANK() OVER (
PARTITION BY department
ORDER BY salary DESC
) as dept_salary_rank
FROM employees
ORDER BY department, dept_salary_rank;
-- Results:
-- name department salary dept_salary_rank
-- Sarah Chen Engineering 125000.00 1
-- Mike Johnson Engineering 115000.00 2
-- Anna Smith Engineering 115000.00 2
-- Carlos Rodriguez Marketing 88000.00 1
-- Tom Brady Sales 105000.00 1
-- Lisa Wang Sales 95000.00 2
Now each department has its own ranking hierarchy. Sarah ranks #1 in Engineering, Carlos ranks #1 in Marketing, and Tom ranks #1 in Sales. The rankings reset for each partition.
This approach is dramatically more efficient than the pre-8.0 alternative involving correlated subqueries or self-joins.
Practical Use Cases
RANK shines in several common scenarios. Let’s explore the most valuable one: finding the top N items per category.
Consider an e-commerce database tracking product sales by region:
CREATE TABLE product_sales (
product_id INT,
product_name VARCHAR(100),
region VARCHAR(50),
sales_amount DECIMAL(12, 2)
);
INSERT INTO product_sales VALUES
(1, 'Laptop Pro', 'North', 450000),
(2, 'Wireless Mouse', 'North', 125000),
(3, 'USB-C Hub', 'North', 89000),
(4, 'Monitor 27"', 'North', 230000),
(5, 'Laptop Pro', 'South', 380000),
(6, 'Wireless Mouse', 'South', 145000),
(7, 'USB-C Hub', 'South', 67000),
(8, 'Monitor 27"', 'South', 210000),
(9, 'Keyboard Mech', 'South', 98000);
To find the top 3 products by sales in each region, we need to rank within partitions, then filter:
WITH ranked_products AS (
SELECT
product_name,
region,
sales_amount,
RANK() OVER (
PARTITION BY region
ORDER BY sales_amount DESC
) as sales_rank
FROM product_sales
)
SELECT
region,
product_name,
sales_amount,
sales_rank
FROM ranked_products
WHERE sales_rank <= 3
ORDER BY region, sales_rank;
-- Results:
-- region product_name sales_amount sales_rank
-- North Laptop Pro 450000.00 1
-- North Monitor 27" 230000.00 2
-- North Wireless Mouse 125000.00 3
-- South Laptop Pro 380000.00 1
-- South Monitor 27" 210000.00 2
-- South Wireless Mouse 145000.00 3
This pattern—rank in a CTE, filter in the outer query—is fundamental to working with window functions.
RANK in Subqueries and CTEs
You cannot filter window function results directly in a WHERE clause. This fails:
-- This will NOT work
SELECT name, salary, RANK() OVER (ORDER BY salary DESC) as rnk
FROM employees
WHERE rnk <= 3; -- ERROR: Unknown column 'rnk' in 'where clause'
Window functions are evaluated after WHERE clauses, so the rank doesn’t exist yet. You have two solutions: subqueries or CTEs (Common Table Expressions).
Subquery approach:
SELECT *
FROM (
SELECT
name,
department,
salary,
RANK() OVER (ORDER BY salary DESC) as salary_rank
FROM employees
) ranked
WHERE salary_rank <= 3;
CTE approach (preferred for readability):
WITH ranked_employees AS (
SELECT
name,
department,
salary,
RANK() OVER (ORDER BY salary DESC) as salary_rank
FROM employees
)
SELECT name, department, salary, salary_rank
FROM ranked_employees
WHERE salary_rank <= 3;
CTEs make complex queries more maintainable. You can chain multiple CTEs, reference them multiple times, and the query intent becomes clearer.
For multiple ranking operations:
WITH dept_rankings AS (
SELECT
name,
department,
salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank
FROM employees
),
overall_rankings AS (
SELECT
*,
RANK() OVER (ORDER BY salary DESC) as overall_rank
FROM dept_rankings
)
SELECT *
FROM overall_rankings
WHERE dept_rank = 1 -- Top earner in each department
ORDER BY overall_rank;
Common Pitfalls and Best Practices
Version Requirements: RANK requires MySQL 8.0 or later. If you’re stuck on 5.7 or earlier, you’ll need user variables or complex subqueries—neither is pleasant.
Performance Considerations: Window functions can be expensive on large datasets. Always index columns used in ORDER BY and PARTITION BY clauses:
CREATE INDEX idx_dept_salary ON employees(department, salary DESC);
-- This index supports:
-- RANK() OVER (PARTITION BY department ORDER BY salary DESC)
NULL Handling: NULLs sort first with ASC, last with DESC. Be explicit about NULL handling:
SELECT
name,
COALESCE(bonus, 0) as bonus,
RANK() OVER (ORDER BY COALESCE(bonus, 0) DESC) as bonus_rank
FROM employees;
Choosing the Right Function:
- Use RANK for competitive rankings where ties should create gaps (sports leaderboards, sales rankings)
- Use DENSE_RANK when you need consecutive ranks regardless of ties (categorical groupings)
- Use ROW_NUMBER when you need unique identifiers or want to break ties arbitrarily
Limit Result Sets Early: Don’t rank millions of rows just to take the top 10. While you still need to rank everything, combining with appropriate WHERE clauses on other columns can reduce the working set:
WITH recent_sales AS (
SELECT *
FROM product_sales
WHERE sale_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
),
ranked_sales AS (
SELECT
*,
RANK() OVER (PARTITION BY region ORDER BY sales_amount DESC) as rnk
FROM recent_sales
)
SELECT * FROM ranked_sales WHERE rnk <= 10;
RANK is a powerful tool that simplifies complex analytical queries. Master the partition-rank-filter pattern, understand when gaps matter for your use case, and you’ll write cleaner, more efficient SQL. The window function approach isn’t just more elegant than pre-8.0 alternatives—it’s often significantly faster, especially with proper indexing.