Get second highest salary from each deptno

select * from
(
select EmployeeID, EmployeeName, Department, Salary, 
rank () over (partition by Department order by Salary desc) r
from Tbl_Employees
)
where r = 2

2nd way

with CTE as(select row_number() over(partition by deptno order by salary desc) row,salary,deptno from empname) 
select * from CTE where row=2 

3rd way

SELECT
    * --TODO - pick columns
FROM
    (
        SELECT *,DENSE_RANK() OVER (PARTITION BY DeptNo ORDER BY Salary DESC) as Rnk
        FROM Table
    )
WHERE
    Rnk = 2