All Articles

Nth Highest Salary in SQL: Every Method Explained

Query Patterns
#nth-highest-salary#window-functions#dense-rank#sql-interview#sql-patterns

Why This Is One of the Most-Asked SQL Interview Questions

"Find the Nth highest salary" is arguably the single most recognizable SQL interview question — and precisely because it looks simple, it's an effective filter for how deeply a candidate actually understands ordering, ties, and NULLs. There are several valid solutions, each with different tradeoffs, and interviewers often ask you to solve it two or three different ways in the same conversation to see how flexible your SQL vocabulary is.

The Sample Data

-- employees
| id | name    | salary |
|----|---------|--------|
| 1  | Alice   | 95000  |
| 2  | Bob     | 95000  |  -- tied with Alice
| 3  | Charlie | 88000  |
| 4  | Diana   | 76000  |
| 5  | Evan    | 76000  |  -- tied with Diana
| 6  | Farah   | 65000  |

Note the intentional ties — Alice/Bob and Diana/Evan. This is the detail that separates a correct solution from a naive one, since most wrong answers to this question fail silently the moment there's a tie.

Method 1: DISTINCT + ORDER BY + OFFSET/LIMIT

-- Second highest distinct salary
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
OFFSET 1 LIMIT 1;

-- Result: 88000 (Charlie's salary — correctly skips the tied 95000 pair as one value)

Why DISTINCT matters: without it, OFFSET 1 LIMIT 1 on the raw (non-distinct) salary column would return 95000 again — the second row, not the second distinct value — because Alice and Bob both occupy a ranked position. This is the single most common mistake on this question.

N is just the offset: for the 3rd highest, use OFFSET 2 LIMIT 1; the pattern generalizes directly.

Method 2: Correlated Subquery with COUNT(DISTINCT ...)

A classic alternative that works on virtually every SQL engine, including older versions without window function support:

SELECT DISTINCT salary
FROM employees e1
WHERE 2 - 1 = (
  SELECT COUNT(DISTINCT salary)
  FROM employees e2
  WHERE e2.salary > e1.salary
);

-- Generalized for any N: count how many distinct salaries are strictly greater;
-- the Nth highest is the salary where exactly N-1 distinct salaries beat it.

This is more verbose than Method 1 but is a strong answer to show when an interviewer specifically asks "how would you do this without LIMIT/OFFSET?" — some engines (older SQL Server versions, for instance) don't support that syntax.

Method 3: Window Functions — DENSE_RANK (the recommended modern approach)

This is the cleanest, most readable, and most requested solution in modern interviews, and it naturally handles ties correctly.

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

-- Result: 88000

Why DENSE_RANK, not RANK or ROW_NUMBER? This is one of the most important distinctions on this entire topic:

  • DENSE_RANK() — ties share a rank, and the next rank has no gap (95000→1, 95000→1, 88000→2). This is almost always what "2nd highest salary" means in plain English: the second-highest distinct value.
  • RANK() — ties share a rank, but the next rank skips (95000→1, 95000→1, 88000→3). Using this here would make you skip straight past 88000 when asked for "rank 2" — it wouldn't exist in the result at all.
  • ROW_NUMBER() — every row gets a unique number regardless of ties (95000→1, 95000→2, 88000→3). Using this would return Bob's 95000 as the "2nd highest," which is usually not the intended answer when the data has ties.

Getting this distinction right, unprompted, is one of the strongest signals you can give an interviewer on this question.

Getting the Full Row, Not Just the Salary

Real interview follow-ups almost always ask for the associated employee, not just the number:

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

-- Result: both Diana AND Evan are NOT included here since they're rank 3 —
-- but if the tie were AT rank 2, both tied names would correctly appear

This is actually a meaningful business decision, not just a technical one: if two people are tied for 2nd, should the query return one row or both? DENSE_RANK correctly returns both when the tie is at the requested rank — which is usually the right behavior, and worth stating explicitly to the interviewer.

Nth Highest Per Group (a very common follow-up)

"Find the 2nd highest salary in each department" — add PARTITION BY:

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

This "top-N per group" pattern is one of the highest-value things to have fluent, since it's asked in dozens of disguises: top 3 products per category, most recent 5 orders per customer, highest-paid employee per department, and so on.

A Reusable Stored Function for Any N (bonus)

If a role is more backend/platform-engineering focused, showing you can wrap this into a reusable function is a strong signal:

CREATE OR REPLACE FUNCTION nth_highest_salary(n INT)
RETURNS TABLE(name TEXT, salary NUMERIC) AS $$
BEGIN
  RETURN QUERY
  SELECT e.name, e.salary FROM (
    SELECT emp.name, emp.salary, DENSE_RANK() OVER (ORDER BY emp.salary DESC) AS rnk
    FROM employees emp
  ) e
  WHERE e.rnk = n;
END;
$$ LANGUAGE plpgsql;

-- Usage: SELECT * FROM nth_highest_salary(2);

Handling NULLs

If salary can be NULL (e.g. unpaid interns, incomplete records), decide deliberately whether they should count:

-- Explicitly exclude NULLs before ranking, so they never occupy a rank slot
SELECT name, salary FROM (
  SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
  WHERE salary IS NOT NULL
) ranked
WHERE rnk = 2;

Most databases sort NULL as either highest or lowest depending on the engine and direction (PostgreSQL treats NULL as largest by default in DESC order unless you specify NULLS LAST) — filtering them out explicitly avoids relying on that default behavior at all.

Common Mistakes

  • Forgetting DISTINCT with OFFSET/LIMIT (Method 1) — the single most common failure on this question, silently wrong only when the data has ties.
  • Using RANK() instead of DENSE_RANK() when the intended meaning is "Nth distinct value" — RANK() introduces gaps that can skip the value entirely.
  • Not testing against data with ties. A query that looks correct on clean, unique salary data can be silently wrong the moment two employees earn the same amount — always test edge cases explicitly.
  • Hardcoding N inside the query instead of parameterizing it, when the real requirement is "any N," not just "2nd" or "3rd."

Common Interview Questions on This Pattern

  1. Find the 2nd highest salary without using LIMIT or TOP. Method 2 — the correlated subquery approach — is the standard answer.
  2. Why would DENSE_RANK and RANK give different answers for "2nd highest"? Explain the ranking gap that RANK() introduces on ties, as detailed above.
  3. How would you find the 2nd highest salary in each department? Add PARTITION BY department to the window function.
  4. What happens if there's only one distinct salary value in the table and someone asks for the 2nd highest? The query correctly returns zero rows — a good answer explicitly states this rather than assuming a fallback value.

Frequently Asked Questions

Which method should I actually use in production code?

The DENSE_RANK window function approach (Method 3) — it's the most readable, correctly handles ties by default, and generalizes cleanly to "top N per group" without rewriting the query structure.

Does OFFSET/LIMIT work the same way across databases?

Mostly, but syntax varies: PostgreSQL and MySQL use LIMIT n OFFSET m; SQL Server uses OFFSET m ROWS FETCH NEXT n ROWS ONLY or the older TOP syntax. The window-function approach (Method 3) is more portable across engines.

Practice This Pattern

This question rewards precision more than cleverness — get the tie-handling right and everything else follows. Try it hands-on, including the top-N-per-group variant, with our SQL practice questions, or apply it to realistic payroll and sales data in our case studies. For the underlying ranking functions, see our SQL interview questions guide.

Back to All Articles