Find the second distinct highest salary
Return one row containing the second distinct highest non-NULL salary. Return NULL when fewer than two distinct salaries exist. Return only the requested columns and follow the specified ordering. This question probes find the overall maximum, then take the maximum strictly below it; the aggregate yields null when there is no candidate.
Input
Setup script defining employees(id INT, name VARCHAR(30), department_id INT, manager_id INT, salary INT).
Output
second_highest; exactly one row.
Constraints
0–10000 rows per table. IDs are unique and non-NULL unless stated otherwise; foreign keys may be NULL. Numeric amounts are integers from 0 to 1000000. Dates are valid YYYY-MM-DD text. Text uses case-sensitive comparison; test data has consistent case. Duplicate business values and NULLs are allowed unless excluded by the task.
Example
Tables and sample rows
CREATE TABLE employees(id INT, name VARCHAR(30), department_id INT, manager_id INT, salary INT); INSERT INTO employees VALUES (1,'Ana',10,NULL,100),(2,'Ben',10,1,200),(3,'Cara',20,1,200),(4,'Dan',20,3,300),(5,'Eve',NULL,NULL,NULL);
Expected result
second_highest 200
Another dataset (1)
CREATE TABLE employees(id INT, name VARCHAR(30), department_id INT, manager_id INT, salary INT); INSERT INTO employees VALUES (1,'Solo',10,NULL,100),(2,'Twin',10,1,100),(3,'Null',NULL,NULL,NULL);
Expected result
second_highest NULL
Covers missing matches, ties, or NULL values without relying on insertion order.
Hints (2)
- Exclude the largest salary with a strict inequality.
- MAX over an empty set returns NULL.
Target complexity: time O(n) · space O(1)