Second Highest Salary

Hook: This is the SQL version of asking, "Who is next after the tallest person?" — interviewers love it because one duplicate value can quietly break a naive answer.

Question: How do you find the second highest salary in a table?

Answer: The cleanest idea is to first find the highest salary, then look for the highest salary that is still smaller than that. That gives the second distinct salary, which is usually what interviewers mean. If there is no second distinct salary, the result should be NULL.

Interview-Ready Answer: I would treat this as the second distinct salary, not the second row. My go-to query is to find the maximum salary that is less than the overall maximum salary, which naturally skips duplicates and returns NULL when a second distinct salary does not exist. It is simple, readable, and works well in real databases.

What the question really means

In interviews, second highest salary usually means the second highest distinct value. If three employees earn 100000, 100000, and 90000, the second highest salary is 90000, not the second row in sorted order. That distinction is the main trap.

How the classic solution works under the hood

1. Find the maximum salary in the table. This is the top value.
2. Ignore every row whose salary equals that maximum. That removes all ties for first place.
3. From the remaining rows, find the maximum salary again. That is now the largest value below the top one, so it is the second highest distinct salary.
4. If there are no remaining rows, return NULL. That tells the caller there is no second distinct salary.

One simple way to say it in SQL is: MAX(salary) WHERE salary < (SELECT MAX(salary) ...). The inner query finds the tallest salary; the outer query finds the tallest salary below it.

Why this is better than just sorting

A lot of candidates jump straight to ORDER BY salary DESC LIMIT 1 OFFSET 1. That can work only if you first remove duplicates, because otherwise two people sharing the highest salary can make the second row still be the same top salary. The aggregate approach avoids that mistake by design.

When to use each pattern

• Use MAX below MAX when you want the second distinct value and want a compact answer.
• Use DENSE_RANK() when you need the rank number for many rows, not just one value.
• Use sorting with OFFSET only if you are sure duplicates are handled, or you are intentionally asking for the second row, not the second distinct salary.

Performance and complexity

The aggregate solution is usually conceptually O(n) time because the database must inspect the rows to compute the maximums. Extra space is typically O(1) from the query writer's point of view, although the database optimizer may use internal buffers. A sort-based solution is often closer to O(n log n) if the engine must sort all salaries. On a table with millions of rows, that difference can matter a lot.

If salary is indexed, some databases can answer top-value queries faster by reading a small range of the index instead of sorting the whole table. That said, interview answers should still focus on correctness first: get the distinct meaning right, then mention the possible index benefit as a bonus detail.

Important edge cases

• All salaries are the same: there is no second distinct salary, so return NULL.
• Only one row exists: also returns NULL.
• Null salaries: aggregate functions like MAX ignore NULL, which is usually what you want.
• Negative salaries or decimal values: the same logic still works, because ordering does not care whether the numbers are positive or negative.

Memory model: think of a podium. First find the gold medalist, then put everyone else back on the floor and find the next tallest person. That is the whole trick.

Real-World Example: Imagine an HR analytics service inside a large payroll system. It generates a report showing the second highest salary in each department so managers can spot pay gaps. The query matters because the report drives real decisions, not just a coding exercise.

One production bug came from using ORDER BY salary DESC LIMIT 1 OFFSET 1 without thinking about duplicates. Two directors shared the top salary, so the query returned the same highest value again instead of the next distinct salary. The dashboard looked plausible, but the pay-gap report was wrong, and managers saw the wrong benchmark for weeks.

What the incident looked like: support tickets said the dashboard was "stuck" on the top number, logs showed repeated top salaries, and finance noticed the second-place value never changed even when a new employee moved into that band. The user impact was a misleading compensation report, which is exactly the kind of silent data bug that SQL interviews are trying to protect you from.

Follow-up & Tricky Questions:

• What if there are duplicate top salaries? The second highest salary should still be the next distinct value. That is why the MAX below MAX pattern is safer than simply skipping one sorted row.
• How would you write this with window functions? Use DENSE_RANK() over ORDER BY salary DESC and pick rank 2. DENSE_RANK gives the same rank to equal salaries, which makes ties easy to handle.
• How do you return the employee name, not just the salary? First compute the second highest distinct salary, then join back to the employee table on that salary. If multiple employees share it, you will get multiple rows, which is often correct.
• What should happen if there is no second highest salary? Return NULL. In interviews, this is often the expected behavior because there is no valid second value to report.
• Can LIMIT 1 OFFSET 1 be correct? Yes, but only if you first deduplicate the salaries or use a ranking method. Without that, duplicates can make the answer wrong.
• What if salary values include NULL? Most aggregate solutions ignore NULL automatically, so they still work. Just be careful if you use sorting, because you may need to know where your database places NULL values.

• Tricky: Is the second highest salary the same as the second row after sorting? No. If the top salary appears twice, the second row can still be the top salary, so you must think in terms of distinct values.
• Tricky: Does MAX(salary) < (SELECT MAX(salary) ...) return the second row? No. It returns the largest value smaller than the maximum, which is exactly why it correctly skips ties.
• Tricky: If there are two salaries tied for first place, what is the second highest? It is the next smaller distinct salary, not one of the tied top salaries. Ties do not create a new rank in a distinct-value interpretation.

Common Mistakes:

• Mistake: Using ORDER BY salary DESC LIMIT 1 OFFSET 1 and forgetting duplicates. Correction: Use DENSE_RANK() or filter by values below the maximum.
• Mistake: Returning the second row instead of the second distinct salary. Correction: Always ask whether duplicates should count as separate positions.
• Mistake: Not handling the no-second-value case. Correction: Return NULL when there is only one distinct salary.
• Mistake: Writing a solution that is hard to explain. Correction: Keep the mental model simple: find the top value, then find the top value below it.

Memory Hook: “Tallest first, then tallest below the tallest.” Picture a podium: remove everyone on the top step, then choose the highest person left.

Cheat Sheet:

• Second highest usually means second distinct salary.
• Classic SQL idea: MAX(salary) where salary is less than the overall MAX(salary).
• Duplicates at the top should not change the answer.
• If no second distinct value exists, return NULL.
• DENSE_RANK() is the best ranking alternative.
• Sorting with OFFSET is fine only if duplicates are handled.

Practice Tasks:

• Write the query using the MAX below MAX pattern.
• Rewrite it with DENSE_RANK() and compare the two results on duplicated salaries.
• Add a test case where all salaries are equal and confirm that the answer is NULL.