Second Highest Salary

Hook: Interviewers love this question because it looks tiny, but it tests whether you understand ties, NULLs, and window functions all at once.

Question: How do I find the second highest salary?

Answer: Use a window function such as DENSE_RANK() to rank salaries from highest to lowest. Then pick the salary with rank 2, which gives the second distinct salary, not just the second row. If there is no second distinct salary, the result should be NULL.

Interview-Ready Answer: I would rank the salaries in descending order with DENSE_RANK(), because it keeps duplicate salaries on the same rank. Then I would select the row where the rank is 2, which gives me the second highest distinct salary, and returns NULL if that salary does not exist.

What the question is really asking

Detailed Explanation: This is not asking for the second physical row after sorting. It is asking for the second distinct salary value, which is why duplicates matter. If the top salary appears three times, the answer is still the next lower salary, not one of the tied rows.

1. Remove NULL salaries first, because a missing salary should not compete with real values.
2. Sort salaries from largest to smallest.
3. Assign a rank using a window function. A window function is a function that looks across a set of related rows without collapsing them into one row.
4. Use DENSE_RANK(), which gives equal salaries the same rank and does not skip numbers.
5. Pick rank 2. That is the second highest distinct salary.
6. If rank 2 does not exist, return NULL.

Why DENSE_RANK() is the right tool

DENSE_RANK() is ideal because ties share the same rank. That means two employees with salary 100000 both get rank 1, and the next salary 90000 gets rank 2. This matches the business meaning of second highest salary.

Function	Ties	Gaps	Best use
DENSE_RANK	Same rank	No gaps	Second distinct value
RANK	Same rank	Yes, gaps	Competition style ranking
ROW_NUMBER	No ties	N/A	Pick a physical row

Important subtlety: ROW_NUMBER() is the wrong choice here if you want the second highest salary. It numbers rows one by one, so duplicate top salaries can push the real second distinct salary to row 3 or 4.

How it works under the hood

1. The database reads the salary values.
2. It sorts them in descending order, which is the expensive step.
3. It compares each salary to the previous one in that sorted order.
4. If the salary changes, DENSE_RANK() increments the rank by exactly 1.
5. If the salary repeats, the rank stays the same.
6. The outer query keeps only rank 2, or returns NULL if rank 2 never appears.

When to use this pattern

Use this pattern whenever the wording says second highest distinct, top N distinct, or rank within a group. It also scales nicely to per-department questions by adding PARTITION BY department_id, which means the ranking restarts inside each department.

Performance and practical notes

The main cost is sorting. On a table with 10,000 rows, this is usually trivial. On a table with 1,000,000 rows, the database still has to order a lot of data, so the query is roughly O(n log n) because of the sort, with the window pass itself being linear. Space usage depends on the engine, but the sort can require memory or temporary disk space if the data does not fit in memory.

If there is an index on salary, the optimizer may use it to help the ordering step, but you should not assume that window functions become free. They still need a logically ordered view of the data.

Edge cases interviewers love

• All salaries are NULL: the answer is NULL.
• Only one distinct salary exists: the answer is NULL.
• Top salary appears many times: DENSE_RANK() still gives the next distinct salary rank 2.
• Negative or decimal salaries: the same logic works; ranking only cares about ordering.

Real-World Example: Imagine a payroll analytics dashboard in an HR platform. Managers open it to see the top salary bands in each department, including the second highest salary so they can compare compensation fairness. The query is also used in automated alerts that flag departments where salary gaps are unusually large.

One common production bug happens when a developer uses ROW_NUMBER() instead of DENSE_RANK(). If two engineers both earn 180000, the next salary 165000 should still be the second highest salary, but ROW_NUMBER() can make it look like the answer is tied to the second physical row instead. The dashboard then shows the wrong number, and HR starts asking why the report does not match payroll.

What goes wrong in the incident: support tickets spike, the report log shows duplicated top salaries, and users complain that the compensation dashboard changes after every refresh even though salaries did not change. In the worst case, a bonus rule built on the wrong ranking can trigger incorrect payouts or misleading executive reports.

Follow-up & Tricky Questions:

• Follow-up: Why not use ROW_NUMBER()? Because it counts rows, not distinct values. If the highest salary appears more than once, ROW_NUMBER() can point to a duplicate instead of the next lower salary.
• Follow-up: What if I want the second highest salary per department? Add PARTITION BY department_id inside the window function. That restarts ranking for each department, so each group gets its own rank 1, rank 2, and so on.
• Follow-up: How do I ignore NULL values? Filter them out with WHERE salary IS NOT NULL before ranking. NULL means unknown, so it should not affect the top salary logic.
• Follow-up: How would you do this without window functions? You can use SELECT MAX(salary) with a subquery that finds the maximum salary below the overall maximum, but that approach is less flexible for grouped rankings.
• Follow-up: What does the database return if there is no second salary? A single-row result with NULL, which is usually the expected interview answer.
• Tricky: If two people tie for the highest salary, is the second highest salary the same value? No. The next smaller distinct salary is still rank 2. Ties do not consume extra ranks in DENSE_RANK().
• Tricky: Does RANK() work the same as DENSE_RANK() here? Not always. RANK() leaves gaps after ties, so if the highest salary is duplicated, the next salary may become rank 3 instead of rank 2.
• Tricky: Can I just use ORDER BY salary DESC LIMIT 1 OFFSET 1? Only if you first remove duplicates, because otherwise OFFSET 1 can still land on the same highest salary. It is a row-based shortcut, not a distinct-value ranking.

Common Mistakes:

• Mistake: Using ROW_NUMBER() for a distinct salary question. Correction: Use DENSE_RANK() so tied salaries share the same rank.
• Mistake: Forgetting to ignore NULL. Correction: Filter NULL salaries out before ranking.
• Mistake: Assuming the second row is the second highest salary. Correction: Rows and values are different; duplicates change the answer.
• Mistake: Returning multiple rows for the same salary. Correction: If the problem wants one value, collapse the result to a single scalar answer.

Memory Hook: Think of a podium with ties: DENSE_RANK() gives every tied person the same medal number, and the next unique score gets the next number with no gap.

Cheat Sheet:

• Second highest salary usually means second highest distinct value.
• Use DENSE_RANK() OVER (ORDER BY salary DESC).
• Filter NULL salaries before ranking.
• Pick rank 2, or return NULL if it does not exist.
• ROW_NUMBER() counts rows; DENSE_RANK() counts distinct levels.

Practice Tasks:

• Rewrite the query to return the third highest distinct salary.
• Adapt it to return the second highest salary per department.
• Test the query on a table where every salary is the same and confirm that the result is NULL.