Query to Find the Second highest salary of an Employee

How to find the employee whose salary is second highest. For example, in the EMP table, “JONES” has the second-highest salary as 3065.00. 

Let's check out few ways to find the second highest salary from the emp table.

1) SELECT DISTINCT SAL FROM EMP E1 WHERE 2=(SELECT COUNT(distinct SAL) FROM EMP E2 WHERE E1.SAL<=E2.SAL); Output:

2) SELECT * FROM  (SELECT ENAME, SAL, DENSE_RANK() OVER (ORDER BY SAL DESC) RANK FROM EMP) a WHERE a.RANK=2; Output:

3) SELECT ENAME, SAL FROM EMP e1 WHERE N-1 = (SELECT COUNT(DISTINCT SAL) FROM EMP e2 WHERE e2.SAL > e1.SAL); Note: replace N with the highest salary number Using this method of finding out the second-highest salary is slow because of the execution of the inner query again and again. Output: