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.

---

2. 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.

---

3. 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.

---

4. 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.