3 ways to find second highest salary ORACLE SQL

3 Ways to find second highest salary ORACLE SQL.

Solution 1 :

WITH T AS
(
SELECT SAL,ENAME,
   DENSE_RANK() OVER (ORDER BY SAL Desc) AS Rnk
FROM EMP
)
SELECT SAL,ENAME
FROM T
WHERE Rnk IN (2);
--WHERE Rnk IN (1,2,3);

OR

SELECT ENAME,SAL, RANK FROM(
SELECT  ENAME,SAL,DENSE_RANK() OVER (ORDER BY SAL DESC) RANK FROM EMP
) WHERE RANK=2

Solution 2 :

SELECT MAX(SAL) FROM EMP WHERE SAL NOT IN (SELECT MAX(SAL) FROM EMP)

Solution 3 :

SELECT MAX(SAL) FROM EMP WHERE SAL  IN 
(SELECT SAL FROM EMP 
MINUS
SELECT MAX(SAL) FROM EMP)

comments powered by Disqus