How to use the ROW_NUBER() Function In MySQL
This method utilizes window functions to calculate a unique row number for each record with the criteria of choosing the department-wise highest salary because it can filter out the top salary records where the employee’s salary is higher than the department average.
-- SQL Server
SELECT *
FROM (
SELECT e.*, AVG(e.Salary) OVER (PARTITION BY e.Department) AS AvgDepartmentSalary,
ROW_NUMBER() OVER (ORDER BY e.EmployeeID DESC) AS row_num
FROM Employees e
) AS subquery
WHERE Salary > AvgDepartmentSalary AND row_num <= 5;
Output:
Explanation:
- We use the AVG() window function partitioned by the department to calculate the departmental average salary.
- The ROW_NUMBER() window function assigns a row number to each record, ordered by EmployeeID in descending order.
- The outer query selects employees whose salary is greater than their departmental average salary and limits the output to 5 rows.
How to Find Employees with Salaries Higher Than Their Departmental Average in MySQL
In businesses and institutions, comparisons of employees’ salaries to their colleagues within the same department is a major value concern. Suppose we are an employee in a large organization where designed teams work differently: Engineering, Marketing, or Sales.
For example, suppose there are cases in which one person receives a larger salary than another who worked in the same department and that’s precisely what we are going to discuss further on.
In this article, We’ll find how we discover the salary of each worker in every department which is higher than the average salary provided in that department.