# 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).

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.