← SQL roadmap

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.

IntermediateJunior · 1–3 yearsRanking

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.

  1. Find the maximum non-null salary.
  2. Filter salaries strictly less than it.
  3. 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?
Practise SQL with the AI mentor

Can't find a SQL topic you need for exams or placements? Request it and we'll add it.