How do you find the Nth highest salary? | Interview Question

Q: How do you find the Nth highest salary?

**Target Audience:** Beginners meeting ranking queries for the first time, and candidates who want the tie, NULL and empty-result details interviewers use to separate answers. **Difficulty:** Medium **Engines:** SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. MySQL and SQL Server behaviour is cited from their manuals. > **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. Every result shown was produced by actually running it; the **Verified** section quotes the example output in full. ## 1. Why This Even Matters — A Story First At a running race the organisers hand out medals. Two runners cross the line together in first place. Who gets bronze? - If medals go to **finishing times**, the two winners share gold, the next time is silver, the one after is bronze. - If medals go to **position

The problem

Question presented to candidate: "Given an employees table with a salary column, write a query that returns the third highest salary. Then make it work for any N, and tell me what your query does when two people earn the same amount, when some salaries are NULL, and when there are fewer than N distinct salaries."

What a strong answer should cover:

Clarify "Nth highest": the Nth distinct salary value (ties count once) or the Nth row; the classic answer means distinct values
DENSE_RANK() OVER (ORDER BY salary DESC) and filter = N, which returns every employee at that salary; why ROW_NUMBER() and RANK() give different answers on ties
Without window functions: SELECT DISTINCT salary ... ORDER BY salary DESC LIMIT 1 OFFSET N-1, or the correlated "N-1 distinct salaries are higher" subquery
Fewer than N salaries: a bare LIMIT/OFFSET returns no row; wrapping it in a scalar subquery returns NULL instead
NULL salaries: PostgreSQL sorts NULL first in DESC order, so the "highest" can be NULL unless you filter it or write NULLS LAST; SQLite, MySQL and SQL Server sort it last in DESC
Per group (Nth highest per department) with PARTITION BY, and the cost: one sort of the table, helped by an index on salary

Clarifying questions expected:

If two employees share the top salary, is the next one the 2nd or the 3rd highest?
Should the result be the salary value or the employees who earn it?
What should come back when there is no Nth salary: no row, or NULL?

Code / implementation expected: Yes — a DENSE_RANK() query, and one approach that works without window functions.

Schema used by the examples:

nth-highest

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Beginners meeting ranking queries for the first time, and candidates who want the tie, NULL and empty-result details interviewers use to separate answers. Difficulty: Medium Engines: SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. MySQL and SQL Server behaviour is cited from their manuals.

How to read this doc: Every idea is explained in plain language first, then tagged with 📌 Interview term: so you also learn the exact words an interviewer expects. Every result shown was produced by actually running it; the Verified section quotes the example output in full.

1. Why This Even Matters — A Story First

At a running race the organisers hand out medals. Two runners cross the line together in first place. Who gets bronze?

If medals go to finishing times, the tw

The solution is hidden so you can try first

8 min read · 12 sections

Run the code

Edit the query and run it against the tables for this question.

SQLROW_NUMBER vs RANK vs DENSE_RANK on tied salaries, then the 3rd highest with DENSE_RANK

SQLDISTINCT + LIMIT/OFFSET wrapped in a scalar subquery: NULL when there is no Nth salary

SQLNo window functions, no LIMIT: exactly N-1 distinct salaries are higher

PostgreSQL trap: NULL sorts first in DESC order, so it takes rank 1

SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS dense
FROM employees
ORDER BY salary DESC, id
LIMIT 4;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.