MySQL Query to Find Highest Paid Employees

Retrieving information about top earners is a fundamental database operation used in payroll management, financial reporting, and HR analytics. This article demonstrates how to write efficient MySQL queries to find the highest-paid employees from a database table, covering scenarios for finding the single top earner, the top N earners, and handling salary ties using standard SQL techniques.

The Employee Table Structure

To demonstrate the queries, assume we have an employees table with the following structure:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    salary DECIMAL(10, 2)
);

Retrieve the Single Highest-Paid Employee

To find the employee who earns the absolute highest salary, sort the table by the salary column in descending order and limit the result to just one row.

SELECT first_name, last_name, salary 
FROM employees 
ORDER BY salary DESC 
LIMIT 1;

In this query: * ORDER BY salary DESC sorts the records from the highest salary to the lowest. * LIMIT 1 ensures that only the first record (the highest earner) is returned.

An alternative approach using a subquery with the MAX() function is useful if you want to find all employees who earn the maximum salary in case of a tie:

SELECT first_name, last_name, salary 
FROM employees 
WHERE salary = (SELECT MAX(salary) FROM employees);

Retrieve the Top N Highest-Paid Employees

If you need to retrieve a specific number of top earners (for example, the top 5 highest-paid employees), you simply increase the LIMIT value.

SELECT first_name, last_name, salary 
FROM employees 
ORDER BY salary DESC 
LIMIT 5;

Changing the number after LIMIT allows you to customize exactly how many top records you want to retrieve.

Handling Salary Ties (MySQL 8.0+)

If multiple employees earn the same salary, a simple LIMIT query might exclude employees with identical earnings. To handle ties fairly, you can use the DENSE_RANK() window function available in MySQL 8.0 and later.

WITH RankedEmployees AS (
    SELECT first_name, last_name, salary,
           DENSE_RANK() OVER (ORDER BY salary DESC) as salary_rank
    FROM employees
)
SELECT first_name, last_name, salary, salary_rank
FROM RankedEmployees
WHERE salary_rank <= 3;

This query ranks employees based on their salary without skipping ranks. Anyone sharing the same salary will receive the same rank, ensuring that all tied employees in the top three salary brackets are accurately represented in the output.