My solution was very similar to another submitted solution. However, I really prefer CTEs over sub queries. Another benefit is Oracle's sub query factoring (not in use here), which will usually be more efficient (but not always!).

with rankedSalaries as (
    select emps.*, dense_rank() over(partition by department order by salary desc) rank
    from PLC2_Employees emps
)
select EmployeeID, EmployeeName, Department, Salary
from rankedSalaries
where rankedSalaries.rank = 2
order by Department, EmployeeID

Tags: