How to Find the Second Highest Salary in SQL? (SQL Interview Deep Dive)

"Find the second highest salary" is the most-asked SQL query question in fresher and lateral interviews alike, and it is deceptively deep. This deep dive gives you five correct approaches, explains which one to lead with, and walks through the duplicate-value and NULL traps that quietly fail most candidates.

Why interviewers ask about the second highest salary

The question hides a lot of SQL behind a one-line prompt. To answer it well you must reach for a subquery or a window function, decide what "second highest" means when salaries tie, and handle the case where no second value exists.

Because it has several valid solutions, it is a great filter: a weak candidate knows one memorized query; a strong one offers two or three and explains the trade-offs. That is why it survives across years of interviews.

Throughout, assume a simple employees(id, name, salary) table. If subqueries or window functions are shaky for you, skim the subqueries tutorial before rehearsing these answers.

How to answer this in an interview

Lead with the subquery answer because it runs on every database and handles duplicates. Then say "if window functions are allowed, DENSE_RANK is cleaner and generalizes to the Nth salary." Offering a progression shows range.

Before the interviewer asks, name the two edge cases: duplicate top salaries and no second value. Stating them yourself signals seniority. Write the query, then trace it against a tiny four-row table out loud.

Q1. What is the safest query for the second highest salary?

Use a subquery that finds the maximum salary strictly below the overall maximum: SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees). It works on every SQL database and treats duplicate top salaries as one.

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

The inner query finds the top salary; the outer query finds the biggest value below it. Because it uses MAX, ten people earning the top salary still collapse to one, and the answer stays correct.

Interview note: Follow-up: "what does this return if everyone earns the same?" NULL — there is no salary below the max — which is the desired, error-free behaviour.

Q2. How do you solve it with LIMIT and OFFSET?

Sort distinct salaries in descending order and skip the first one: SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1. The DISTINCT is essential so duplicate top salaries do not occupy both positions.

SELECT DISTINCT salary AS second_highest
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

This is concise and reads well, but two cautions: it is MySQL/PostgreSQL syntax (SQL Server uses OFFSET ... FETCH), and forgetting DISTINCT returns the top salary again when it is duplicated.

Interview note: Trap: "why DISTINCT here?" Without it, if the highest salary appears twice, OFFSET 1 lands on the second copy of the top value, not the second highest value.

Q3. How do you use DENSE_RANK, and why is it the best answer?

DENSE_RANK() assigns rank 1 to the highest salary, rank 2 to the next distinct salary with no gaps, and so on. Selecting rank 2 gives the second highest and generalizes instantly to any Nth position.

SELECT salary AS second_highest
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) ranked
WHERE rnk = 2;

DENSE_RANK is the interviewer's favourite because it says exactly what you mean: "the second distinct salary". Learn the mechanics in the window functions introduction.

Interview note: Follow-up: "why DENSE_RANK and not RANK?" RANK leaves gaps after ties (two rank-1 rows make the next rank 3), so WHERE rnk = 2 might return nothing.

Q4. What is the difference between ROW_NUMBER, RANK and DENSE_RANK for this problem?

ROW_NUMBER numbers every row uniquely even on ties, RANK leaves gaps after ties, and DENSE_RANK leaves no gaps. Only DENSE_RANK reliably puts the second distinct salary at rank 2.

Consider salaries 100, 100, 90. ROW_NUMBER gives 1, 2, 3 — so "row 2" is 100, wrong. RANK gives 1, 1, 3 — so there is no rank 2 at all. DENSE_RANK gives 1, 1, 2 — so rank 2 is correctly 90.

SELECT salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn,
       RANK()       OVER (ORDER BY salary DESC) AS rk,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dr
FROM employees;

Interview note: Trap: candidates who reach for ROW_NUMBER here quietly return the wrong answer whenever the top salary is duplicated.

Q5. How do you find the Nth highest salary in general?

Take the DENSE_RANK solution and change the filter to WHERE rnk = N. This is the cleanest generalization because it counts distinct salary values, not rows.

SELECT salary AS nth_highest
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) ranked
WHERE rnk = 3;   -- third highest

With LIMIT you would write LIMIT 1 OFFSET N-1 over SELECT DISTINCT salary, but the window-function version is easier to reason about and adjust.

Interview note: Follow-up: "how does your query change for the 5th highest?" Just change 2 to 5; the structure is identical — that reusability is the point.

Q6. How do you solve it with a correlated subquery?

Count how many distinct salaries are strictly greater than each salary; the second highest is the one with exactly one salary above it: WHERE (SELECT COUNT(DISTINCT s2.salary) FROM employees s2 WHERE s2.salary > s1.salary) = 1.

SELECT DISTINCT salary AS second_highest
FROM employees s1
WHERE (
  SELECT COUNT(DISTINCT s2.salary)
  FROM employees s2
  WHERE s2.salary > s1.salary
) = 1;

Set the count to N-1 for the Nth highest. This is a classic pre-window-function technique. It is elegant but slow on large tables because the inner query runs once per outer row.

Interview note: Trap: "why COUNT(DISTINCT salary) and not COUNT(*)?" Without DISTINCT, duplicate higher salaries inflate the count and break the logic.

Q7. How do you handle duplicate salaries correctly?

First clarify what "second highest" means: the second distinct salary value, or the second-ranked employee. For the second distinct value — what interviewers almost always want — use DENSE_RANK or the MAX-less-than-MAX subquery, both of which ignore duplicates.

If salaries are 100, 100, 90, the second highest salary is 90. A ROW_NUMBER or a LIMIT without DISTINCT would wrongly return 100. Naming this ambiguity before writing code is exactly the judgment interviewers reward.

Interview note: Follow-up: "which employees earn the second highest salary?" Now you want the rows, so join back on the value: WHERE salary = (that second highest value).

Q8. What should the query return when there is no second highest salary?

It should return NULL, not an error or a confusing empty set. The MAX-less-than-MAX subquery returns NULL naturally when all salaries are equal or the table has one row.

The DENSE_RANK and LIMIT approaches return zero rows in that case, which you may need to wrap so the caller still gets a single NULL. Deciding this behaviour deliberately is part of a complete answer.

-- Returns NULL, not empty, when no second value exists
SELECT MAX(salary) FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

Interview note: Trap: "does your LIMIT query return NULL or nothing when there is only one salary?" Nothing — it returns no rows, which may break code expecting a value.

Q9. Which approach performs best on a large table?

On a large, indexed table the MAX-less-than-MAX subquery and the window-function version are usually the most efficient; the correlated COUNT subquery is the slowest because it re-runs per row.

An index on salary helps MAX and ORDER BY ... LIMIT significantly. Window functions still scan and sort but do it once. Mentioning that you would check the query plan shows practical maturity.

Interview note: Follow-up: "why is the correlated subquery slow?" It executes the inner aggregate once for every outer row — roughly O(n²) without good indexing.

Q10. How is this question related to joins and grouping?

Once you find the second highest salary value, listing who earns it is a filter or a self-relationship — closely tied to joins and grouping. Ranking within each department is the same idea with PARTITION BY.

SELECT department_id, salary
FROM (
  SELECT department_id, salary,
         DENSE_RANK() OVER (PARTITION BY department_id
                            ORDER BY salary DESC) AS rnk
  FROM employees
) t
WHERE rnk = 2;   -- second highest per department

The PARTITION BY extension — second highest per department — is the natural follow-up. It connects to the SQL joins interview set and grouping fundamentals.

Interview note: Trap: "find the second highest salary in each department" — the only change is adding PARTITION BY department_id; candidates who hard-coded one query freeze here.

How to prepare

Create a five-row employees table with a deliberate duplicate top salary and run all five approaches. Watch ROW_NUMBER, RANK and DENSE_RANK diverge on the tie — that single experiment locks in the whole topic.

Rehearse the answer as a progression: subquery first, then LIMIT OFFSET, then DENSE_RANK, then the Nth-highest and per-department variations. Then practise the surrounding material in the SQL interview queries collection and the SQL freshers set. This kind of query-writing fluency is exactly what the database module of the Java Full Stack with AI course is built to develop.