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 second_high as ( select Department, Salary,row_number() over(partition by Department order by Department, Salary) high from @Employees ) select b.* from second_high a join @Employees b on a.Department=b.Department and a.Salary=b.Salary where high=2
Tags: