Table: Employee
+-------------+------+
| Column Name | Type |
+-------------+------+
| id | int |
| salary | int |
+-------------+------+
id is the primary key (column with unique values) for this table.
Each row of this table contains information about the salary of an employee.
Write a SQL query to find the second highest distinct salary from the Employee
table. If there is no second highest salary, return null
.
For example:
Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
Output:
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200 |
+---------------------+
Another example:
Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1 | 100 |
+----+--------+
Output:
+---------------------+
| SecondHighestSalary |
+---------------------+
| null |
+---------------------+
The most straightforward way to find the second highest salary is to first find all distinct salaries, sort them in descending order, and then select the second element. If there are fewer than two distinct salaries, return null
.
SELECT MAX(salary) AS SecondHighestSalary
FROM Employee
WHERE salary < (SELECT MAX(salary) FROM Employee);
This SQL query first finds the maximum salary. Then, it selects the maximum salary from the remaining salaries that are less than the maximum salary found in the first step.
Employee
table. The subquery SELECT MAX(salary) FROM Employee
takes O(n) time. The outer query SELECT MAX(salary) FROM Employee WHERE salary < ...
also takes O(n) in the worst-case scenario.NULL
, and the final result will be NULL
.NULL
.NULL
.An improved solution uses LIMIT
and OFFSET
to directly retrieve the second highest distinct salary. This approach is often more efficient, especially for large tables.
SELECT DISTINCT salary AS SecondHighestSalary
FROM Employee
ORDER BY salary DESC
LIMIT 1 OFFSET 1;
To handle the case where there is no second highest salary, we can wrap the above query in another SELECT
statement that returns NULL
if the inner query returns an empty set. Using IFNULL
SELECT
IFNULL(
(SELECT DISTINCT salary
FROM Employee
ORDER BY salary DESC
LIMIT 1 OFFSET 1), NULL) AS SecondHighestSalary;
SELECT DISTINCT salary FROM Employee
: This selects all distinct salaries from the Employee
table.ORDER BY salary DESC
: This sorts the distinct salaries in descending order.LIMIT 1 OFFSET 1
: This selects the first row (LIMIT 1) after skipping the first row (OFFSET 1), effectively selecting the second highest salary.IFNULL(..., NULL)
: This handles the case where the subquery returns an empty set (i.e., there is no second highest salary). If the subquery returns NULL
, the outer query returns NULL
. Using IFNULL
ensures the correct output according to the problem description when there is no second highest salary.LIMIT
queries, potentially making it more efficient than the naive solution, especially for larger datasets. Often this can be closer to O(n).NULL
.OFFSET
will cause the query to return an empty set, and the IFNULL
function will return NULL
.NULL
because the OFFSET
will skip the only distinct value.