Topic 49 of 52 · Intermediate
Find the second distinct highest salary
Return one row containing the second distinct highest non-null salary, or NULL if absent.
MySQL 8-compatible SELECT syntax; sample queries also execute in SQLite 3. Window functions require MySQL 8+.
Problem statement
Return one row containing the second distinct highest non-null salary, or NULL if absent.
Compensation tiers must ignore tied employees.
Input: employees(id, dept, salary).
Output: One scalar second_salary.
- Duplicates count as one salary tier.
- An absent second tier produces a NULL scalar.
Sample 1
Input: 100,100,80 Output: 80
The second distinct tier is 80.
Sample 2
Input: 100,100,NULL Output: NULL
Only one known distinct tier exists.
Brute-force solution
Sort and deduplicate all salary values, then offset by one; this can require a full sort.
O(n log n) without ordering indexes.
CREATE TABLE employees(id INTEGER PRIMARY KEY, dept VARCHAR(10), salary INTEGER);
INSERT INTO employees VALUES (1,'A',100),(2,'A',100),(3,'A',80),(4,'B',60),(5,'B',NULL);
SELECT (SELECT DISTINCT salary FROM employees WHERE salary IS NOT NULL ORDER BY salary DESC LIMIT 1 OFFSET 1) second_salary;Optimized solution
Two scalar MAX aggregates exclude the maximum tier without ranking every row.
- Find the maximum non-null salary.
- Filter salaries strictly less than it.
- Return MAX of the remaining values.
CREATE TABLE employees(id INTEGER PRIMARY KEY, dept VARCHAR(10), salary INTEGER);
INSERT INTO employees VALUES (1,'A',100),(2,'A',100),(3,'A',80),(4,'B',60),(5,'B',NULL);
SELECT MAX(salary) second_salary FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);Complexity analysis
Time: O(n) for two scans without indexes; optimizer-dependent.
Auxiliary space: O(1) aggregate state.
Test cases
- No employees.
- One tier.
- Negative salaries if allowed by business schema.
Expected scalar: 80.Common mistakes
- Using offset over duplicated salaries.
- Returning no row instead of the required NULL scalar.
Key takeaways
- Clarify distinct tiers.
- Scalar aggregates retain a row.
- Handle insufficient values.
Follow-up challenges
- How would you generalize to Nth distinct salary?
- Can an index answer MAX efficiently?