SQL Problem · Intermediate

Second Highest Salary in SQL

Solve the second-highest-salary problem with duplicate-safe SQL, null handling, alternatives, and performance reasoning.

Portable SQL with a window-function alternativeReviewed 2026-08-17

The problem

Given an Employee table, return the second-highest distinct salary. If no second distinct salary exists, return NULL rather than returning no row.

Why this matters

This small problem tests several habits that matter in real data work: defining what “second” means when values repeat, choosing whether the result must always contain a row, and selecting a query shape whose behavior you can explain before you optimize it.

Schema and sample data

Employee

idINTEGER	nameVARCHAR	salaryINTEGER
1	Asha	90000
2	Ben	120000
3	Chen	120000
4	Diego	105000
5	Eleni	95000

id — Unique employee identifier.

name — Employee display name.

salary — Annual salary. Assume non-null values for the core exercise.

Expected output

second_highest_salary
105000

Think before SQL

Predict the result first

Do not write SQL yet. Sort the distinct salary values mentally: 120000, 105000, 95000, 90000. Notice that two employees earn 120000, but that value occupies only one rank when the requirement says “distinct salary.” What query operation will remove the current maximum before you ask for the next maximum?

Show a hint

First isolate the maximum salary. Then look only at salaries lower than that value and ask for the maximum of the remaining set. An aggregate such as MAX still returns one row whose value becomes NULL when the remaining set is empty.

Primary solution

Aggregate + scalar subquery

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

The inner query computes the highest salary once as a scalar value.

The WHERE clause removes every row tied at that maximum, which is why duplicate top salaries do not create the wrong answer.

The outer MAX chooses the largest value that remains. If every employee has the same salary, the filtered set is empty and MAX returns NULL in a single result row.

Trade-offs

The intent is compact and portable across major relational databases.
It answers exactly one scalar question, but it is less reusable when you need the third, fourth, or Nth distinct value.

Alternative solutions

DENSE_RANK for a general ranking model

SELECT MAX(salary) AS second_highest_salary
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
  FROM Employee
) ranked
WHERE salary_rank = 2;

DENSE_RANK gives equal salaries the same rank, so both 120000 rows receive rank 1 and 105000 receives rank 2.

Wrapping the ranked rows in MAX preserves the requirement to return one row with NULL when rank 2 does not exist.

The pattern extends naturally to Nth-ranked values and can expose the ranked rows for richer analysis.
It usually requires sorting the salary set, so it can do more work than the focused aggregate query for this one scalar answer.

DISTINCT + ordered offset

SELECT (
  SELECT DISTINCT salary
  FROM Employee
  ORDER BY salary DESC
  OFFSET 1 ROW FETCH NEXT 1 ROW ONLY
) AS second_highest_salary;

DISTINCT collapses duplicate salary values before sorting, then the offset skips the highest distinct salary.

The outer scalar subquery keeps a one-row result when there is no second value.

This is visually close to the human procedure of deduplicate, sort, and take item two.
LIMIT/OFFSET/FETCH syntax varies among database products, so use the form supported by your target engine.

Common mistakes

Using ROW_NUMBER() without deduplicating salaries

ROW_NUMBER assigns a different position to each employee row. Two employees tied for the highest salary can therefore occupy positions 1 and 2, producing the highest salary again instead of the second distinct salary.

ORDER BY salary DESC and take the second row

This ranks employees, not distinct salary values. Duplicate top salaries change the result.

Returning zero rows when no second salary exists

The problem contract asks for a scalar result containing NULL. Query shape is part of correctness, not merely formatting.

Performance reasoning

For large tables, an index whose leading key is salary can help the database locate extreme values, although the optimizer and database engine determine the exact access path.
Do not add an index solely because this exercise contains MAX. In a real system, measure the workload and consider write cost, storage, selectivity, and existing indexes.
If you need many ranks or top-N results, a window-function plan can be more useful than repeating nested aggregate queries, even when the single-value query is simpler.

Independent practice

Change the requirement

Modify the requirement to return the third-highest distinct salary. Solve it once with DENSE_RANK and once without a window function. Then explain which version communicates the requirement more clearly and what happens when fewer than three distinct salaries exist.