To solve this problem, we need to retrieve the top-earning employee from each department, ordered by department name.

 To solve this problem, we need to retrieve the top-earning employee from each department, ordered by department name. The solution involves using a window function to rank employees within each department by their salary and then selecting the highest earner from each group.

Approach

1. Join Tables: Combine the employees and departments tables to get the department names associated with each employee.

2. Rank Employees: Use the ROW_NUMBER() window function to rank employees within each department based on their salary in descending order. To handle ties (employees with the same salary), we break the tie by sorting on employee_id in ascending order.

3. Filter Top Earners: Select only the employees ranked first in their respective departments.

4. Order Results: Sort the final result by department name alphabetically.

Solution Code

sql

WITH ranked_employees AS (
    SELECT 
        d.name AS department_name,
        e.id AS employee_id,
        e.first_name,
        e.last_name,
        e.salary,
        ROW_NUMBER() OVER (
            PARTITION BY e.department_id 
            ORDER BY e.salary DESC, e.id ASC
        ) AS rn
    FROM employees e
    JOIN departments d ON e.department_id = d.id
)
SELECT 
    department_name,
    employee_id,
    first_name,
    last_name,
    salary
FROM ranked_employees
WHERE rn = 1
ORDER BY department_name;

Explanation

1. Common Table Expression (CTE): The ranked_employees CTE joins the employees and departments tables to combine employee details with their department names. The ROW_NUMBER() function assigns a unique rank to each employee within their department. Employees are ordered by:

   • salary DESC to prioritize higher salaries.

   • employee_id ASC to break ties, ensuring consistent results by selecting the employee with the smallest ID first.

2. Main Query: Filters the results from the CTE to include only the top-ranked employees (rn = 1) from each department.

3. Ordering: The final result is ordered by department_name to meet the specified output requirement.