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
select top 100 percent DENSE_RANK() OVER (PARTITION BY Department ORDER BY salary DESC) RANK1,*from @Employees ORDER BY Salary DESC
SELECT EmployeeID,EmployeeName,Department,Salary FROM cte WHere RANK1=2