SQL Interview Prep · Lesson

Second Highest Salary, Five Ways

Subquery, LIMIT/OFFSET, and window-function solutions compared.

The Question Everyone Gets

"Find the second highest salary" is the single most-asked SQL interview question. Interviewers love it because it has many correct answers and several subtle traps.

Assume an employee table with columns id and salary. Your job: return the second highest distinct salary value.

If salaries are 300, 200, 200, 100, the answer is 200, not the second row.
If there is no second distinct salary, the expected answer is usually NULL.

Over the next scenes we will solve it five different ways and discuss when each shines.

CREATE TABLE employee (
  id     INT PRIMARY KEY,
  salary INT
);

Way 1: MAX of values below the MAX

The most intuitive solution: the second highest salary is the largest salary that is strictly less than the overall maximum.

This reads almost like English and works in every SQL dialect. The inner subquery finds the top value, and the outer MAX finds the biggest value below it.

Bonus: if there is no second distinct salary, the outer MAX aggregates zero rows and returns NULL automatically. That free NULL is exactly what interviewers want.

SELECT MAX(salary) AS second_highest
FROM employee
WHERE salary < (SELECT MAX(salary) FROM employee);

All lessons in this course

← Back to SQL Interview Prep