2nd Highest Salary

Method 1

Correlated sub queries are more generalized to find n'th highest or nth lowest salary.


To find nth highest salary

SELECT * FROM Employee E1 WHERE n-1 =

(SELECT COUNT(DISTINCT Salary) FROM Employee E2 WHERE E1.Salary < E2.Salary)

To find 2nd highest salary

SELECT * FROM Employee E1 WHERE 1 =

(SELECT COUNT(DISTINCT Salary) FROM Employee E2 WHERE E1.Salary < E2.Salary)

To find 2nd lowest salary

SELECT * FROM Employee E1 WHERE 1 =

(SELECT COUNT(DISTINCT Salary) FROM Employee E2 WHERE E1.Salary > E2.Salary)

To find nth lowest salary

SELECT * FROM Employee E1 WHERE n-1 =

(SELECT COUNT(DISTINCT Salary) FROM Employee E2 WHERE E1.Salary > E2.Salary)

Method 2
 
HIGHEST 2nd Salary :


;WITH CTEs

AS(SELECT DENSE_RANK() OVER(ORDER BY SALARY DESC) 'Rank',* FROM EMP)

SELECT Names,Salary FROM CTEs WHERE Rank=2

LOWESE 3Rd Salary :


;WITH CTEs

AS(SELECT DENSE_RANK() OVER(ORDER BY SALARY) 'Rank',* FROM EMP)

SELECT Names,Salary FROM CTEs WHERE Rank=3

Note: Desc is removed.