Second highest salary in Oracle SQL

To find the second highest salary in Oracle SQL, you can use various approaches. Below are a few commonly used methods:

1. Using ROW_NUMBER() Window Function

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

Explanation:

The inner query assigns a sequential ROW_NUMBER() to each row ordered by salary in descending order.

The outer query retrieves the row with rn = 2, which represents the second highest salary.

1. Using RANK() Window Function

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

Explanation:

RANK() assigns the same rank to rows with equal salary values.

If there are ties, the next rank will skip values (e.g., 1, 1, 3).

The outer query retrieves the row with rnk = 2.

1. Using Subquery with DISTINCT and ROWNUM

SELECT MAX(salary) AS second_highest_salary
FROM (
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
)
WHERE ROWNUM = 2;

Explanation:

The subquery orders the salaries in descending order and removes duplicates using DISTINCT.

ROWNUM selects the second row, which represents the second highest salary.

1. Using MAX and Subquery

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

Explanation:

The subquery retrieves the highest salary.

The outer query fetches the maximum salary that is less than the highest, effectively giving you the second highest.

Choose the Method Based on the Requirement

If there are potential ties and you need to account for them, use RANK() instead of ROW_NUMBER().

For performance optimization, using MAX with a subquery might be more efficient, but it might not work well with ties.