Second Highest Salary - SQL

In the realm of SQL challenges, the "Second Highest Salary" problem is a popular one. It's an excellent example of how to work with sorting and limiting results in SQL queries. In this post, we'll dive into the problem and provide solutions in MySQL, PostgreSQL, and Oracle. 

Problem Overview 

The task is to find the second highest salary from the Employee table. The query should return null if there is no second-highest salary (meaning there is only one unique salary). 

The Employee Table Structure

id	salary
1	100
2	200
3	300

SQL Query Solutions 

To solve this, we need to consider two main scenarios: 

When There Are At Least Two Different Salaries: Select the second highest one. 

When There Is Only One Salary or None: Return null. 

A common approach to finding the second highest salary is to use a subquery with ORDER BY and LIMIT (or equivalent in different SQL dialects) clauses. 

MySQL Solution 

In MySQL, you can use the LIMIT clause:

SELECT
    (SELECT DISTINCT salary
     FROM Employee
     ORDER BY salary DESC
     LIMIT 1 OFFSET 1) AS SecondHighestSalary;

This query selects distinct salaries, orders them in descending order, and uses LIMIT 1 OFFSET 1 to get the second row, which is the second-highest salary. 

PostgreSQL Solution 

PostgreSQL uses a similar approach to MySQL:

SELECT
    (SELECT DISTINCT salary
     FROM Employee
     ORDER BY salary DESC
     LIMIT 1 OFFSET 1) AS SecondHighestSalary;

Oracle Solution 

Oracle doesn't support the LIMIT clause directly, but we can use ROWNUM:

SELECT MAX(salary) AS SecondHighestSalary
FROM (
    SELECT salary, ROWNUM as rn
    FROM (
        SELECT DISTINCT salary
        FROM Employee
        ORDER BY salary DESC
    )
    WHERE ROWNUM = 2
);

This nested query first selects distinct salaries and orders them. The outer query then selects the salary where ROWNUM is 2, which corresponds to the second-highest salary. 

Expected Output 

Example 1:

For the first example with salaries 100, 200, and 300, the output would be:

Second Highest Salary

SecondHighestSalary
200

Example 2:

For the second example with a single salary 100, the output would be:

Second Highest Salary

SecondHighestSalary
null

Conclusion 

The "Second Highest Salary" problem is a classic SQL query challenge that tests your understanding of sorting, filtering, and handling edge cases. Depending on the SQL dialect, the syntax may vary, but the underlying logic remains the same. This problem is a great way to get comfortable with subqueries and the nuances of different SQL functionalities.