1SELECT TOP 1 salary
2FROM (
3SELECT DISTINCT TOP N salary
4FROM #Employee
5ORDER BY salary DESC
6) AS temp
7ORDER BY salary
1Here is the solution for nth highest
2salary from employees table
3
4SELECT FIRST_NAME , SALARY FROM
5(SELECT FIRST_NAME, SALARY, DENSE_RANK() OVER
6(ORDER BY SALARY DESC) AS SALARY_RANK
7FROM EMPLOYEES)
8WHERE SALARY_RANK = n;
1SELECT MAX(SALARY) 'SECOND_MAX' FROM EMPLOYEES
2WHERE SALARY <> (SELECT MAX(SALARY) FROM EMPLOYEES);
3
4
5OR
6
7Here is the solution for nth highest
8salary from employees table
9
10SELECT FIRST_NAME , SALARY FROM
11(SELECT FIRST_NAME, SALARY, DENSE_RANK() OVER
12(ORDER BY SALARY DESC) AS SALARY_RANK
13FROM EMPLOYEES)
14WHERE SALARY_RANK = n;
1Here is the solution for 3rd highest
2salary from employees table
3
4SELECT FIRST_NAME , SALARY FROM
5(SELECT FIRST_NAME, SALARY, DENSE_RANK() OVER
6(ORDER BY SALARY DESC) AS SALARY_RANK
7FROM EMPLOYEES)
8WHERE SALARY_RANK = 3;
1SELECT ename,sal from Employee e1 where
2 N-1 = (SELECT COUNT(DISTINCT sal)from Employee e2 where e2.sal > e1.sal)
3