0Pricing
SQL Interview Prep · Lesson

Nth Highest With DENSE_RANK

Generalizing to the Nth distinct value and handling duplicates.

Nth Highest With DENSE_RANK is a free SQL Interview Prep lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Generalizing to the Nth Highest

Once you can find the second highest salary, interviewers immediately push: "Now give me the Nth highest." The cleanest, most defensible answer uses DENSE_RANK.

The pattern is always the same: rank distinct salaries in descending order, then filter for the row whose rank equals N. Because the logic does not change with N, this one approach answers the whole family of questions.

We will build it up, handle ties and duplicates, and discuss why DENSE_RANK is the right ranking function for "distinct value" semantics.

The core template

Here is the reusable Nth-highest template. Replace the constant with whatever N the interviewer asks for.

You compute DENSE_RANK in an inner query (the window function cannot live in WHERE), then filter for rnk = N outside. For the 3rd highest salary, set the filter to rnk = 3.

SELECT salary AS nth_highest
FROM (
  SELECT salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employee
) ranked
WHERE rnk = 3;

How DENSE_RANK numbers distinct values

DENSE_RANK assigns the same rank to equal values and never leaves a gap afterwards. That is exactly the "Nth distinct value" definition interviewers mean.

For salaries 800, 800, 600, 600, 400:

  • 800 -> rank 1
  • 600 -> rank 2
  • 400 -> rank 3

So the 3rd highest is 400, even though there are five rows. Duplicates are folded into a single rank automatically.

Why RANK gives the wrong answer

Swap in RANK and the answer breaks. RANK leaves gaps proportional to the number of ties.

For 800, 800, 600, 600, 400:

  • 800, 800 -> rank 1 (two of them)
  • 600, 600 -> rank 3 (gap, no rank 2)
  • 400 -> rank 5

Filtering for rnk = 3 returns 600, and rnk = 2 returns nothing. Unless the interviewer specifically wants competition-style ranking, DENSE_RANK is correct for "Nth distinct salary."

Why ROW_NUMBER is also wrong here

ROW_NUMBER assigns a unique number to every row, ignoring ties entirely. For 800, 800, 600, 600, 400 it produces 1, 2, 3, 4, 5.

So rn = 3 returns 600, but rn = 2 returns the duplicate 800, not a distinct second value. ROW_NUMBER answers "the Nth row" not "the Nth distinct value."

Use ROW_NUMBER only when the question truly wants a specific row, such as deduplication or top-N-per-group keeping exactly one row.

SELECT salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employee;

Parameterizing N safely

In real code you would not hardcode the rank. Pass N as a parameter and compare against it. The window definition stays identical; only the outer filter is parameterized.

This is also where you can return all tied salaries at rank N: because DENSE_RANK shares a rank across ties, WHERE rnk = N may return multiple rows if several employees share the Nth distinct salary, which is often the desired behavior.

SELECT id, salary
FROM (
  SELECT id, salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employee
) ranked
WHERE rnk = :n;

The correlated-count generalization

The pre-window approach also generalizes: a salary is the Nth highest distinct salary when exactly N - 1 distinct salaries are strictly greater than it.

For the 3rd highest, require exactly 2 distinct higher salaries. This works in older engines without window functions but scales poorly because the inner count re-runs per outer row.

SELECT DISTINCT salary AS nth_highest
FROM employee e
WHERE (
  SELECT COUNT(DISTINCT e2.salary)
  FROM employee e2
  WHERE e2.salary > e.salary
) = 2;

MySQL function form interviewers ask for

The LeetCode-style "Nth highest salary" problem often asks for a stored function returning a single value. The body is just the DENSE_RANK template wrapped to return one salary.

You do not need to memorize exact function syntax in an interview, but recognizing that LIMIT N-1, 1 over distinct salaries is the compact MySQL idiom is worth knowing.

SELECT DISTINCT salary
FROM employee
ORDER BY salary DESC
LIMIT 1 OFFSET 2;  -- N = 3, so OFFSET N-1

Worked example: 4th highest

Salaries: 1000, 900, 900, 700, 500, 500, 300.

Distinct descending with DENSE_RANK:

  • 1000 -> 1
  • 900 -> 2
  • 700 -> 3
  • 500 -> 4
  • 300 -> 5

The 4th highest is 500. Note that both 500 rows share rank 4, so filtering rnk = 4 returns both employees who earn 500 if you select their ids too.

Performance notes

How do the approaches compare at scale?

  • DENSE_RANK: one sort over the data, then a filter. Efficient and the planner can use an index on salary for the ordering.
  • Correlated count: potentially O(n squared) because the inner aggregate runs per row. Avoid on large tables.
  • LIMIT/OFFSET: fast for small N but must still sort, and large offsets scan and discard many rows.

Lead with DENSE_RANK and you rarely go wrong.

Edge cases to mention

Strong candidates call out the edges before being asked:

  • N larger than the count of distinct salaries: the filter matches no rows and returns empty. Lesson 4 covers forcing a single NULL.
  • Ties at rank N: DENSE_RANK returns every tied employee; decide whether that is wanted.
  • N = 1: the template still works and returns the maximum.

Quick Check

Apply the Nth-highest template.

Recap

The Nth highest salary has one go-to answer: rank distinct salaries with DENSE_RANK() OVER (ORDER BY salary DESC) in a subquery, then filter WHERE rnk = N.

  • DENSE_RANK means "Nth distinct value" with ties sharing a rank and no gaps.
  • RANK introduces gaps; ROW_NUMBER counts rows not values.
  • The correlated count = N-1 trick generalizes the same idea without windows but scales poorly.

Always flag the "N exceeds available values" edge case, which we solve next.

Frequently asked questions

Is the “Nth Highest With DENSE_RANK” lesson free?

Yes — the full text of “Nth Highest With DENSE_RANK” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “Nth Highest With DENSE_RANK”?

Generalizing to the Nth distinct value and handling duplicates. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Nth Highest With DENSE_RANK” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Second Highest Salary, Five Ways
  2. Nth Highest With DENSE_RANK
  3. Per-Department Top Earner
  4. Returning NULL When No Nth Value Exists
← Back to SQL Interview Prep