Find third MAX Salary without using Analytic Functions

Steps covered:

1. Find the top 2 salaries
2. Find the maximum salary excluding the top 2
3. Join back to EMP to display the full row

Step 1

First, we fetch the top 2 salary values from the emp table.

SELECT sal
FROM (
    SELECT sal
    FROM emp
    ORDER BY sal DESC
)
WHERE ROWNUM < 3;

Result:

SAL
-----
5000
3000

Step 2

Next, we find the maximum salary that is not part of the top 2 salaries. This gives the next highest salary after the first two.

SELECT MAX(sal) AS sal
FROM emp
WHERE sal NOT IN (
    SELECT sal
    FROM (
        SELECT sal
        FROM emp
        ORDER BY sal DESC
    )
    WHERE ROWNUM < 3
);

Result:

SAL
-----
2975

Step 3

Finally, we join the salary value back to the emp table to display the full employee record.

WITH t AS (
    SELECT MAX(sal) AS sal
    FROM emp
    WHERE sal NOT IN (
        SELECT sal
        FROM (
            SELECT sal
            FROM emp
            ORDER BY sal DESC
        )
        WHERE ROWNUM < 3
    )
)
SELECT a.*
FROM emp a
JOIN t b
ON a.sal = b.sal;

Result:

EMPNO ENAME   JOB       MGR   HIREDATE         SAL   COMM   DEPTNO
----- ------- --------- ----- ---------------  ----  -----  ------
7566  JONES   MANAGER   7839  02-APR-81        2975         20

How it works

• Step 1 gets the highest two salaries.
• Step 2 removes those salaries and finds the next maximum.
• Step 3 displays the employee row with that salary.

Alternative approaches

You can also find the third highest salary using analytic functions such as DENSE_RANK() or ROW_NUMBER().

Key Point:
This method is useful when you want the full employee row for the third highest salary, not just the salary value itself.