Retrieve employees who receive the second highest salary within their department.

Question

Assisting AI · Accepted Answer

A single query can list all such employees:

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

`DENSE_RANK` ensures that ties are handled correctly; if two employees share the highest salary, the next distinct salary gets rank 2. The query runs in O(n) time plus the cost of the window function, which is efficient for most tables.

Retrieve employees who receive the second highest salary within their department.

💡 Model Answer

🎤 Get questions like this answered in real-time