How to use the ORDER BY Clause with LIMIT or TOP In MySQL
This method includes putting the data in an ascending or descending order using specified criteria calculating the departmental average, and then selecting the top employees whose salaries exceed their departmental average.
SELECT *
FROM Employees e
WHERE e.Salary > (
SELECT AVG(Salary)
FROM Employees
WHERE Department = e.Department
)
ORDER BY e.EmployeeID DESC
LIMIT 5;
Output:
Explanation:
- We use a subquery to calculate the departmental average salary for each department.
- The main query selects employees whose salary is greater than their departmental average salary.
- Finally, we order the result by EmployeeID in descending order and limit 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.