Four Different Way to Find nth Highest Salary in Sql Server

Here we are taking example to find out 3^rd Heighest salary.

--**************************************

-- ************* 1st Query *************
--**************************************

SELECT TOP 1 SALARY

FROM
(
SELECT DISTINCT TOP 3 SALARY
FROM Employee
ORDER BY SALARY DESC
)a
Order by salary

--**************************************

-- ************* 2nd Query *************
--**************************************

SELECT * FROM (

SELECT DENSE_RANK() OVER (ORDER BY SALARY DESC) AS rownumber,Salary
FROM Employee ) AS Maxs
WHERE rownumber = 3

--**************************************

-- ************* 3rd Query *************
--**************************************

SELECT * FROM (

SELECT
ROW_NUMBER() OVER (ORDER BY SALARY DESC) AS rownumber,Salary
FROM Employee
) AS foo
WHERE rownumber = 3

--**************************************

-- ************* 4th Query *************
--**************************************

Select *

From Employee E1
Where 3 = (Select Count(Distinct(E2.Salary)) From Employee E2 Where E2.Salary >= E1.Salary)

--***********************************************************

-- ************* For Second Highest Salary Query *************
--************************************************************

SELECT MAX(salary) AS SAL FROM EMPLOYEE WHERE salary<>(SELECT MAX(salary) FROM EMPLOYEE)

--***********************************************************

-- ************* For Understand Differnece between row_number(),rank() and dense_rank()function *************
--************************************************************

SELECT salary

,row_number () OVER (ORDER BY salary DESC) as ROW_NUMBER
,rank () OVER (ORDER BY salary DESC) as RANK
,dense_rank () OVER (ORDER BY salary DESC) as DENSE_RANK
FROM Employee