Retrieve the second highest salary along with the department name.

Question

Assisting AI · Accepted Answer

Use a window function to rank salaries per department and filter for rank 2:

```sql
SELECT DepartmentName,
       SecondHighestSalary
FROM (
    SELECT d.DepartmentName,
           MAX(e.Salary) AS SecondHighestSalary,
           DENSE_RANK() OVER (PARTITION BY d.DepartmentID ORDER BY e.Salary DESC) AS SalaryRank
    FROM Employees e
    JOIN Departments d ON e.DepartmentID = d.DepartmentID
    GROUP BY d.DepartmentID, d.DepartmentName
) AS ranked
WHERE SalaryRank = 2;
```

This returns each department’s second highest salary. The query is linear in the number of employee records plus the cost of the window function, making it efficient for typical workloads.

Retrieve the second highest salary along with the department name.

💡 Model Answer

🎤 Get questions like this answered in real-time