To retrieve the top 3 highest-paid employees in each department using SQL, you can use a Common Table Expression (CTE) or a subquery in conjunction with the ROW_NUMBER() window function. Here’s an example SQL query:
Assuming you have a table called employees with the following structure:
- employee_id
- name
- salary
- department_id
You can write the query as follows:
WITH RankedEmployees AS (
SELECT
employee_id,
name,
salary,
department_id,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM
employees
)
SELECT
employee_id,
name,
salary,
department_id
FROM
RankedEmployees
WHERE
rank <= 3
ORDER BY
department_id, rank;
Explanation of the Query:
1. Common Table Expression (CTE): The query begins with a CTE named RankedEmployees which selects employee data along with a rank.
2. ROW_NUMBER():
- The ROW_NUMBER() function assigns a unique number to each row within a partition (in this case, each department).
- It ranks employees based on their salary in descending order (ORDER BY salary DESC).
3. PARTITION BY: This clause groups the results by department_id. The ROW_NUMBER() will reset the count for each department.
4. Main Query: The main query retrieves data from the CTE where the rank is less than or equal to 3, ensuring you only get the top 3 highest-paid employees for each department.
5. ORDER BY: Finally, the results are ordered by department_id and the rank so that employees are displayed in a structured manner.
You can execute this query in your SQL environment as long as it supports window functions.