RANK
Returns the rank of a row in a window partition, that is, its place in ORDER BY order.
MySQL 8.4
RANK()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.4
SELECT member_name,
status,
RANK() OVER (
ORDER BY status
) AS status_rank
FROM FamilyMembers;Salary places in departments
MySQL 8.4
SELECT department_id,
name,
salary,
RANK() OVER (
PARTITION BY department_id
ORDER BY salary DESC
) AS salary_rank
FROM employees;Details
Without ORDER BY every row of the partition gets rank 1. 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.