Second Highest Salary
Problem
Write a SQL query to report the second highest distinct salary from Employee. If there is no such salary, return NULL.
SQL: SELECT (SELECT DISTINCT salary FROM Employee ORDER BY salary DESC LIMIT 1 OFFSET 1) AS SecondHighestSalary;
salaries = [100, 200, 300]200SELECT (SELECT DISTINCT salary FROM Employee
ORDER BY salary DESC LIMIT 1 OFFSET 1) AS SecondHighestSalary;Explanation
To find the second highest distinct salary, we line up the unique salaries from biggest to smallest and grab the one in second place. The query does exactly that in a few small pieces.
SELECT DISTINCT salary throws away duplicate salaries so ties count as one. ORDER BY salary DESC sorts them with the largest first.
Then LIMIT 1 OFFSET 1 skips the very top salary (the highest) and takes just the next one — that is the second highest distinct value.
The clever part is wrapping all of this in an outer SELECT (...). If there is no second salary, the inner query returns nothing, and a sub-select with no rows evaluates to NULL — which is exactly the answer the problem wants in that case.
Example: salaries [100, 200, 300] sort to [300, 200, 100]; skip 300, take 200. With only one distinct salary, the offset row does not exist, so the result is NULL.