When working with Oracle SQL, we often need to retrieve the Nth highest salary (like 2nd, 3rd, etc.). While analytic functions like RANK() or DENSE_RANK() are commonly used for this, some scenarios or database versions require solutions without analytic functions.

This tutorial shows how to find the 3rd highest salary using only nested subqueries.

Step 1: Create a Sample employees Table

CREATE TABLE employees (
  id NUMBER,
  name VARCHAR2(50),
  salary NUMBER
);

-- Insert sample data
INSERT INTO employees VALUES (1, 'Alice', 5000);
INSERT INTO employees VALUES (2, 'Bob', 7000);
INSERT INTO employees VALUES (3, 'Charlie', 6000);
INSERT INTO employees VALUES (4, 'Diana', 7000);
INSERT INTO employees VALUES (5, 'Eva', 8000);

COMMIT;

Step 2: Query to Find the 3rd Highest Salary (Without Analytic Functions)

SELECT *
FROM employees
WHERE salary = (
  SELECT MAX(salary)
  FROM employees
  WHERE salary < (
    SELECT MAX(salary)
    FROM employees
    WHERE salary < (
      SELECT MAX(salary)
      FROM employees
    )
  )
);

How It Works

Let’s break down the query from the inside out:

1. SELECT MAX(salary) FROM employees
   → Gets the highest salary (1st maximum).
2. SELECT MAX(salary) FROM employees WHERE salary < (...)
   → Gets the 2nd highest salary, by excluding the top salary.
3. The outermost subquery repeats the same logic to get the 3rd highest salary, excluding the top two salaries.
4. Finally, the outer SELECT retrieves all employee rows whose salary matches the 3rd maximum.

Output

Based on the sample data, the result would be:

ID	NAME	SALARY
3	Charlie	6000

If multiple employees had a salary of 6000, all of them would be returned.

Notes

• This method works in all versions of Oracle, including Oracle 11g and earlier.
• It handles duplicate salaries properly.
• To find the 4th, 5th, or Nth highest salary, you just nest more SELECT MAX() queries.
• You can replace salary with any numeric column (like commission, bonus, etc.).