How to Find the Second Highest Salary in SQL (3 Ways)

A classic interview question, explained with a simple query, a LIMIT/OFFSET version, and a window function

"Find the second highest salary from the Employee table" is one of the most common SQL interview questions, and it also appears as LeetCode problem 176. It looks easy, but it hides a few tricky edge cases: duplicate salaries, tables with only one row, and what to return when there simply is no second value. In this post, we'll walk through three solid approaches and when to use each.

The problem

Given an employees table with a salary column, return the second highest distinct salary. If there is no second highest salary, return NULL.

id	name	salary
1	Asha	100
2	Ravi	200
3	Meera	300

The expected answer here is 200.

Approach 1: The nested MAX query

This is the simplest approach and works on virtually every SQL database.

SELECT MAX(salary) AS SecondHighestSalary
FROM employees
WHERE salary < (
  SELECT MAX(salary)
  FROM employees
);

How it works

1. The inner query, SELECT MAX(salary) FROM employees, finds the highest salary.
2. The outer query takes the maximum of all salaries that are strictly lower than that value, which is exactly the second highest.

Why it handles the edge cases

• Duplicate top salaries: if two people share the highest salary, the condition salary < MAX(salary) excludes both, so you still get the next distinct value. That is what "second highest" normally means.
• No second salary: if all salaries are identical, or the table has just one row, the outer query finds no rows to aggregate, and MAX returns NULL. That matches the problem's requirement without any extra code.

One practical note: make sure the table name matches your schema. LeetCode calls it Employee, while many tutorials use employees.

Approach 2: LIMIT and OFFSET

If you prefer to think in terms of sorting, you can order salaries from highest to lowest and skip the first one.

SELECT (
  SELECT DISTINCT salary
  FROM employees
  ORDER BY salary DESC
  LIMIT 1 OFFSET 1
) AS SecondHighestSalary;

How it works

• DISTINCT removes duplicate salaries so each value counts once.
• ORDER BY salary DESC puts the highest first.
• LIMIT 1 OFFSET 1 skips the top row and returns the next one.

Why the outer SELECT?

Without the wrapper, a table with no second salary would return an empty result instead of NULL. Wrapping the query as a scalar subquery turns "no rows" into NULL, which is usually what you want.

This syntax works in MySQL and PostgreSQL. Other databases use different keywords, such as TOP in SQL Server or FETCH FIRST in Oracle.

Approach 3: A window function with DENSE_RANK

Window functions give you the most flexible solution.

SELECT MAX(salary) AS SecondHighestSalary
FROM (
  SELECT salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) t
WHERE rnk = 2;

How it works

DENSE_RANK() assigns a rank to each salary from highest to lowest, and tied salaries share the same rank without leaving gaps. The highest salary gets rank 1, the next distinct salary gets rank 2, and so on. Filtering for rnk = 2 picks out the second highest, and wrapping it in MAX ensures you get NULL when no such rank exists.

Why use it?

This approach scales effortlessly. To find the 3rd or Nth highest salary, you only change the number in WHERE rnk = N. It is also easy to extend to per-department rankings by adding PARTITION BY department.

Which approach should you choose?

Approach	Best for	Portability
Nested MAX	Quick, simple second-highest queries	Works everywhere
LIMIT / OFFSET	Readable, sort-based thinking	MySQL, PostgreSQL
DENSE_RANK	Nth highest, or ranking within groups	Modern databases with window functions

For a straightforward "second highest" question, the nested MAX query is hard to beat. If the interviewer follows up with "now find the Nth highest" or "do it per department," reach for DENSE_RANK.

Common mistakes to avoid

• Using ROW_NUMBER instead of DENSE_RANK: ROW_NUMBER gives tied salaries different numbers, so duplicates at the top would give the wrong answer.
• Forgetting DISTINCT with OFFSET: without it, a duplicated top salary would be returned as the "second" value.
• Not handling the no-result case: always confirm the query returns NULL, not an empty set, when there is no second value.

Final thoughts

The second highest salary problem is a great reminder that simple-looking SQL questions often test your attention to edge cases. Start with the nested MAX query for its clarity, learn the LIMIT/OFFSET version for sort-based thinking, and keep DENSE_RANK in your toolkit for anything more general. Master these three patterns, and a whole family of ranking questions becomes much easier.