Finding the second highest salary in Oracle SQL

Finding the second highest salary in Oracle SQL can also be approached in multiple ways. Here are various methods to achieve this, including problem statements and explanations for each approach.

Problem Statement

Find the second highest salary from the employees table.

Sample Data

Assuming the same employees table:

employee_id	first_name	last_name	salary	department_id
1	John	Doe	60000	10
2	Jane	Smith	70000	20
3	Alice	Johnson	80000	10
4	Bob	Brown	75000	20
5	Charlie	Davis	90000	30

Solution 1: Using a Subquery

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

Explanation: This query first finds the maximum salary and then retrieves the maximum salary that is less than this maximum, effectively giving the second highest salary.

Solution 2: Using ORDER BY with ROWNUM

SELECT salary AS second_highest_salary
FROM employees
ORDER BY salary DESC
OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY;

Explanation: This query orders the salaries in descending order and skips the first row (highest salary) before fetching the next row, which corresponds to the second highest salary.

Solution 3: Using RANK() Function

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

Explanation: This query assigns a rank to each salary in descending order using the RANK() function. It then selects the salary where the rank is 2, giving the second highest salary. This approach is beneficial if multiple employees have the same salary.

Solution 4: Using DENSE_RANK() Function

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

Explanation: Similar to the RANK() example, but this time using DENSE_RANK(), which avoids gaps in ranking when there are ties. This means if two employees have the highest salary, they both get rank 1, and the next unique salary will receive rank 2.

Solution 5: Using GROUP BY with HAVING

SELECT salary
FROM employees
GROUP BY salary
HAVING COUNT(*) > 1
ORDER BY salary DESC
OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY;

Explanation: This approach groups the salaries and uses HAVING to ensure there are at least two occurrences of a salary. It then orders the results and skips the highest salary to fetch the second highest.

Solution 6: Using Common Table Expression (CTE)

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

Explanation: This solution uses a CTE to rank the salaries in descending order and then selects the salary where the rank is 2.

Summary of Solutions

Subquery: Best for a straightforward second highest salary retrieval.

ORDER BY with ROWNUM: Useful for fetching the second highest salary while controlling the row output.

RANK() and DENSE_RANK(): Best for handling ties and when needing to return multiple records sharing the same salary.

GROUP BY with HAVING: Useful when you want to ensure the second highest salary is distinct.

CTE: Good for readability and when reusing computed results in complex queries.

Conclusion

These various methods demonstrate how to find the second highest salary in Oracle SQL. The choice of method often depends on the specific requirements, such as handling ties or ensuring distinct salaries, and provides flexibility based on the structure of your queries.