Second Highest Salary - Solution & Explanation

Problem Overview: Given an Employee table containing employee salaries, return the second highest distinct salary. If a second highest value does not exist, the query must return NULL. The key detail is distinct salary; duplicates should not affect the ranking.

Approach 1: Sort with DISTINCT and LIMIT/OFFSET (O(n log n) time, O(n) space)

This approach relies on sorting the salary column in descending order after removing duplicates. Use SELECT DISTINCT salary, sort using ORDER BY salary DESC, and then skip the highest value using LIMIT 1 OFFSET 1. The database engine performs a sort on the unique salary values, which usually costs O(n log n) time depending on indexing. Wrapping the query in a subquery allows returning NULL when fewer than two distinct salaries exist. This method is straightforward and widely supported across SQL databases.

Example pattern:

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

Approach 2: MAX with Subquery Filtering (O(n) time, O(1) space)

This approach avoids sorting entirely. First compute the maximum salary in the table, then find the largest salary strictly less than that value. The query uses a nested condition like WHERE salary < (SELECT MAX(salary) FROM Employee), followed by another MAX() aggregation. The database scans the table and keeps track of the largest valid value below the top salary. Since no global sorting is required, the logical complexity is O(n) with constant additional space.

Example pattern:

SELECT MAX(salary) AS SecondHighestSalary FROM Employee WHERE salary < (SELECT MAX(salary) FROM Employee);

This pattern works well in relational systems because aggregate functions like MAX() are optimized internally. It also naturally returns NULL when a second distinct salary does not exist.

Both strategies rely on core concepts from SQL such as sorting, aggregation, and nested queries. Understanding how DISTINCT, ORDER BY, and aggregate functions interact with a database execution plan helps you reason about performance.

Recommended for interviews: The MAX() with subquery approach is typically preferred. It demonstrates understanding of SQL aggregation and avoids unnecessary sorting, which signals stronger query optimization instincts. The DISTINCT + ORDER BY solution is still valuable because it shows you understand ranking queries and SQL ordering semantics.