Find Nth Highest Salary in a table and Highest Salary for each department in a table

Find Nth Highest Salary in a table:

CREATE TABLE EMP
(
NAME VARCHAR(20),
SALARY INT,
DEPTNO INT
)

2nd Highest Salary in a table:

;WITH TMPTBL AS(SELECT TOP 2 NAME,SALARY FROM EMP ORDER BY SALARY DESC)
SELECT TOP 1* FROM TMPTBL ORDER BY SALARY 

Or Using Row_Number()

;WITH CTE AS(SELECT ROW_NUMBER() OVER(ORDER BY SALARY DESC) ROW,SALARY,DEPTNO FROM EMP)
SELECT * FROM CTE WHERE ROW = 2

Find Nth Highest Salary for each department in a table:

;WITH CTE AS(SELECT ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY SALARY DESC) ROW,SALARY,DEPTNO FROM EMP)
SELECT * FROM CTE WHERE ROW=2