MySQL 8 Cookbook

Partition results

You can partition the results in the OVER clause. Suppose you want to find out the salary rank for each year; it can be done as follows:

mysql> SELECT hire_date_year, salary, ROW_NUMBER() OVER(PARTITION BY hire_date_year ORDER BY salary DESC) AS 'Rank' FROM employees JOIN salaries ON salaries.emp_no=employees.emp_no ORDER BY salary DESC LIMIT 10;+----------------+--------+------+| hire_date_year | salary | Rank |+----------------+--------+------+|           1985 | 158220 |    1 ||           1985 | 157821 |    2 ||           1986 | 156286 |    1 ||           1985 | 155709 |    3 ||           1987 | 155513 |    1 ||           1985 | 155377 |    4 ||           1985 | 155190 |    5 ||           1985 | 154888 |    6 ||           1985 | 154885 |    7 ||           1985 | 154459 |    8 |+----------------+--------+------+10 rows in set (8.04 sec)

You can notice ...