Monday, 25 August 2014

Find Nth Highest Salary of Employee



SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP n salary
FROM  Tb_EmpDetails
ORDER BY salary DESC) a
ORDER BY salary

method1:
2nd highest salary from table

SELECT TOP 1 Salary FROM
(
      SELECT DISTINCT TOP 2 Salary FROM Tb_EmpDetails ORDER BY Salary DESC
) AS T ORDER BY Salary ASC


get 3rd highest salary from table

SELECT TOP 1 Salary FROM
(
      SELECT DISTINCT TOP 3 Salary FROM Tb_EmpDetails ORDER BY Salary DESC
     ) AS T ORDER BY Salary ASC

et 4th, 5th, 6th...nth salary using the following query structure

SELECT TOP 1 Salary FROM
(
      SELECT DISTINCT TOP N Salary FROM Tb_EmpDetails ORDER BY Salary DESC
) AS T ORDER BY Salary ASC

Method2:
get 2nd highest salary from table

SELECT MAX(Salary) AS 'Salary' FROM Tb_EmpDetails
WHERE Salary NOT IN
(
 SELECT DISTINCT  TOP 1 (SALARY) FROM Tb_EmpDetails ORDER BY Salary DESC
)

get 3rd highest salary from table

SELECT MAX(Salary) AS 'Salary' FROM Tb_EmpDetails
WHERE Salary NOT IN
(
 SELECT DISTINCT  TOP 2 (SALARY) FROM Tb_EmpDetails ORDER BY Salary DESC
)

get 4th, 5th, 6th...nth salary using the following query structure

SELECT MAX(Salary) AS 'Salary' FROM Tb_EmpDetails
WHERE Salary NOT IN
(
 SELECT DISTINCT TOP N-1(SALARY) FROM Tb_EmpDetails ORDER BY Salary DESC
)

Method 3:

get 2nd highest salary from table

SELECT MIN(Salary) AS 'Salary' FROM Tb_EmpDetails
WHERE Salary IN
(
 SELECT DISTINCT  TOP 2 Salary FROM Tb_EmpDetails ORDER BY Salary DESC
)

get 4th, 5th, 6th...nth salary using the following query structure

SELECT MIN(Salary) AS 'Salary' FROM Tb_EmpDetails
WHERE Salary IN
(
 SELECT DISTINCT  TOP N Salary FROM Tb_EmpDetails ORDER BY Salary DESC
)

Method 4:

SELECT MAX(salary)AS 'Salary' FROM Tb_EmpDetails WHERE salary NOT IN (SELECT MAX(salary) FROM Tb_EmpDetails)

Method 5:

SELECT MAX(salary) AS 'Salary' FROM Tb_EmpDetails WHERE salary < (SELECT MAX(salary) FROM Tb_EmpDetails)