RANK
Returns the rank of a row in a window partition, that is, its place in ORDER BY order.
MySQL 8.1
RANK() OVER ([PARTITION BY ...] [ORDER BY ...])Parameters
- PARTITION BYoptional
- Columns that split rows into partitions, ranks are computed in each one separately
- ORDER BYoptional
- Order of rows that defines the place; without it all rows have rank 1
Return value
An integer (BIGINT UNSIGNED), the place of the row; equal values share a place and the next places are skipped: 1, 2, 2, 4
Examples
Rank by status with gaps
MySQL 8.1
SELECT member_name,
status,
RANK() OVER (
ORDER BY status
) AS status_rank
FROM FamilyMembers;Salary places in departments
MySQL 8.1
SELECT department_id,
name,
salary,
RANK() OVER (
PARTITION BY department_id
ORDER BY salary DESC
) AS salary_rank
FROM employees;Details
To select the rows with the top places, compute the rank in a subquery and filter in the outer query: window functions cannot be used in WHERE of the same query. Unlike ROW_NUMBER, rows with equal values get the same place, so the condition «rank = 1» returns all leaders.