The challenge is to find the employees with the second highest salary in each department. However, it is a little more complicated because if two employees have the same salary, you need to list both of them.
Solution to TSQL Beginners Challenge 1
with cte as (select rank() over (partition by department order by salary desc) as rk, EmployeeId, EmployeeName, Department, Salary from @employees) select EmployeeID, EmployeeName, Department, Salary from cte where rk=2 order by department, employeeid
Tags: