How to find 3 employees of each department with max. salary for 1 pass of the base?

Question

SQL

The task is to build a query for the TABLE table (columns: Employee, Department, Salary):
in 1 database pass (that is, as I understand it, without nested sub-queries with SELECT - or am I wrong?) Return 3 employees with the maximum salary in each department.

I have written something like this so far, but the nested query is apparently not suitable + I can’t figure out how to make LIMIT 3 for each department.

(Newbie yet, help would be greatly appreciated)

SELECT t.employee, t.department, t.salary
FROM (
SELECT department, MAX(salary) AS max_salary
FROM table GROUP BY employee
) AS x
INNER JOIN table AS t
ON t.department = x.department AND t.salary = x.max_salary
LIMIT 3;

thank!

Answer 1

SELECT *
FROM
(
SELECT Employee, 
             Department, 
             Salary,
             ROW_NUMBER() OVER(PARTITION BY Department ORDER BY salary DESC) AS rn
FROM table
) AS t1
WHERE rn <= 3