Assume we have a table:

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

-- 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;

Method 1: Using ORDER BY with OFFSET ... FETCH (Oracle 12c+)

SELECT *
FROM employees
ORDER BY salary DESC
OFFSET 1 ROW FETCH NEXT 1 ROW ONLY;

Explanation:

• OFFSET 1 ROW: skips the first highest salary.
• FETCH NEXT 1 ROW: picks the next one, which is the second highest.
• This works correctly only if salaries are distinct. For duplicates (like multiple employees with the same top salary), use a different method.

Method 2: Using DISTINCT with ORDER BY + ROWNUM (Oracle 11g and below)

SELECT MAX(salary) AS second_max_salary
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

Explanation:

• Finds the maximum salary that is less than the absolute max salary.
• Handles duplicate top salaries as well.

To get the full employee details:

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

Method 3: Using ROW_NUMBER() (Oracle 10g+)

SELECT id, name, salary
FROM (
  SELECT id, name, salary,
         ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
  FROM employees
)
WHERE rn = 2;

Explanation:

• Assigns a row number to each employee ordered by salary descending.
• Picks the row where rn = 2, i.e., the second highest salary.
• If two employees have the same highest salary, this returns the second person in order.

Method 4: Using DENSE_RANK() (Handles Ties)

SELECT id, name, salary
FROM (
  SELECT id, name, salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
)
WHERE rnk = 2;

Explanation:

• DENSE_RANK() handles ties: if two employees share the highest salary, both get rank 1, and the next gets rank 2.
• This method returns all employees with the second max salary, even if tied.

Sample Output (Using DENSE_RANK):

For this data:

ID	NAME	SALARY
5	Eva	8000
2	Bob	7000
4	Diana	7000
3	Charlie	6000
1	Alice	5000

The second max salary is 7000, and both Bob and Diana will be returned.

Summary of Methods

Method	Oracle Version	Handles Ties	Returns Full Row
OFFSET ... FETCH	12c+	No	Yes
Subquery with MAX < MAX	All	Yes	Yes
ROW_NUMBER()	10g+	No	Yes
DENSE_RANK()	10g+	Yes	Yes