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:  |
No comments:
Post a Comment