Nth Highest Salary

Nth Highest Salary

What is this problem about?

The Nth Highest Salary SQL problem asks you to find the n-th highest distinct salary from an Employee table. If fewer than n distinct salaries exist, return NULL. This classic SQL problem tests dense ranking, subqueries, and offset-based selection.

Why is this asked in interviews?

Deloitte, Microsoft, Amazon, Google, Bloomberg, and Accenture ask this as a foundational SQL ranking problem. It tests knowledge of DENSE_RANK(), LIMIT/OFFSET, or subquery approaches. The database interview pattern is the core, and multiple valid SQL solutions exist — demonstrating knowledge of several earns extra credit.

Algorithmic pattern used

Multiple approaches:

1. DENSE_RANK: Create a function or CTE using DENSE_RANK() OVER (ORDER BY salary DESC), filter WHERE rank = N.
2. LIMIT/OFFSET: SELECT DISTINCT salary FROM Employee ORDER BY salary DESC LIMIT 1 OFFSET N-1 (wrapped to return NULL if no row).
3. Subquery: Find the salary that has exactly N-1 distinct salaries greater than it.

Example explanation

Salaries: [100, 90, 90, 80, 70]. N=2. Distinct sorted: [100, 90, 80, 70]. 2nd highest = 90.

N=5: only 4 distinct salaries exist. Return NULL.

Common mistakes candidates make

• Not using DISTINCT (counting duplicate salaries as separate ranks).
• OFFSET is 0-indexed: for Nth salary, use OFFSET N-1.
• Not returning NULL when fewer than N distinct salaries exist.
• Using ROW_NUMBER (assigns unique ranks to ties) instead of DENSE_RANK (handles ties as equal rank).

Interview preparation tip

SQL ranking problems come in two flavors: with window functions (DENSE_RANK()) and without (subquery or LIMIT/OFFSET). Know both. The stored procedure approach (CREATE FUNCTION getNthHighestSalary(N INT)) is sometimes required in older SQL platforms. Always clarify: "distinct salaries" means 100, 90, 80 are three distinct values, not four when 90 appears twice. This distinction matters for rank counting.