Find the Nth distinct highest salary
For N = 3, return one row with the third distinct highest non-NULL salary. Return NULL if fewer than three distinct salaries exist. Return only the requested columns and follow the specified ordering. This question probes dense_rank counts distinct salary levels without gaps; parameterize the rank predicate to solve general n.
Input
Setup script defining employees(id INT, name VARCHAR(30), department_id INT, manager_id INT, salary INT).
Output
third_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
third_highest 100
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
third_highest NULL
Covers missing matches, ties, or NULL values without relying on insertion order.
Hints (2)
- Remove NULL salaries before ranking.
- Use DENSE_RANK rather than ROW_NUMBER for distinct levels.
Target complexity: time O(n log n) · space O(n)