SQL Interview Prep · Lesson

Nth Highest With DENSE_RANK

Generalizing to the Nth distinct value and handling duplicates.

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;

All lessons in this course

← Back to SQL Interview Prep