3 ways to find second highest salary ORACLE SQL

August 6, 2021Posted inPLSQL, SQL

Solution 1 :

Copy Code
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

Copy Code
SELECT ename, sal, RANK
  FROM (SELECT ename, sal, DENSE_RANK () OVER (ORDER BY sal DESC) RANK
          FROM emp)
 WHERE RANK = 2

Solution 2 :

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

Solution 3 :

Copy Code
SELECT MAX (sal)
  FROM emp
 WHERE sal IN (SELECT sal FROM emp
               MINUS
               SELECT MAX (sal) FROM emp)
Tags: