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 DENSE_RANK() OVER(PARTITION BY DEPARTMENT ORDER BY SALARY DESC)ROW,EMPLOYEEID,EMPLOYEENAME,SALARY,DEPARTMENT FROM @EMPLOYEES)
SELECT * FROM CTE WHERE ROW=2