ch department has a separate sequence of ranks due to the PARTITION BY
clause. This particular sequence of values for rank() is given by the ORDER BY
clause inside the window function’s OVER clause. Finally, to get our results
in a readable format we order the data by dept and the newly generated ranking
column.

Now, we need to reduce the results to find only the top 5 per department. Here
is a common mistake:

select
rank() over (partition by dept order by salary desc) as ranking,
dept, name, salary
from employee_salaries
where ranking <= 5
order by dept, ranking;

ERROR 1054 (42S22): Unknown column 'ranking' in 'where clause'

Trying to filter only the first 5 values per department by putting a where
clause in the statement does not work, due to the way window functions are
computed. The computation of window functions happens after all WHERE, GROUP
BY and HAVING clauses have been completed, right before ORDER BY, so the WHERE
clause has no idea that the ranking column exists. It is only present after we
have filtered and grouped all the rows.

To counteract this problem, we need to wrap our query into a derived table. We
can then attach a where clause to it:

select *from (select rank() over (partition by dept order by salary desc) as
ranking,
 dept, name, salary
from employee_salaries) as salary_ranks
where (salary_ranks.ranking <= 5)
 order by dept, ranking;
+---------+-------------+--------------+--------+
| ranking | dept        | name         | salary |
+---------+-------------+--------------+--------+
|       1 | Engineering | Dharma       |   3500 |
|       2 | Engineering | Binh         |   3000 |
|       3 | Engineering | Adalynn      |   2800 |
|       4 | Engineering | Samuel       |   2500 |
|       5 | Engineering | Cveta        |   2200 |
|       1 | Sales       | Carbry       |    500 |
|       2 | Sales       | Clytemnestra |    400 |
|       3 | Sales       | Juraj        |    300 |
|       3 | Sales       | Kalpana      |    300 |
|       5 | Sales       | Svantepolk   |    250 |
+---------+-------------+--------------+--------+

URL: https://mariadb.com/kb/en/window-functions-overview/