Detailed explanation
Finding the Nth highest salary usually means finding the Nth highest distinct salary, not the salary in the Nth employee row. Clarify this before choosing a query because duplicate salaries change the result. For example, with salaries 90000, 90000, 75000, and 60000, the third highest distinct salary is 60000. Also clarify whether the interviewer wants one salary value or every employee earning that salary, and assume N is a positive integer.
For databases supporting window functions, DENSE_RANK is a clear solution: SELECT DISTINCT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS r FROM employees WHERE salary IS NOT NULL) t WHERE r = :n; Here, :n represents a parameter whose syntax depends on the database or driver. DENSE_RANK assigns equal salaries the same rank and advances without gaps when the salary changes. The outer query filters the computed rank because window functions generally cannot be used directly in WHERE at the same query level. DISTINCT returns one salary value; without it, tied employees produce repeated values. To return employees instead, include their identifiers and names in the inner query and omit DISTINCT.
A correlated alternative counts how many distinct salaries are greater than each candidate: SELECT DISTINCT e.salary FROM employees e WHERE e.salary IS NOT NULL AND (SELECT COUNT(DISTINCT e2.salary) FROM employees e2 WHERE e2.salary > e.salary) = :n - 1; A salary is Nth highest precisely when N minus one distinct salaries exceed it. For the second highest salary, a simpler nested MAX works: SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees); however, extending repeated MAX queries to arbitrary N becomes cumbersome.
If fewer than N distinct non-NULL salaries exist, the ranking and counting queries return no rows; the aggregate MAX query returns one row containing NULL when no qualifying salary exists. Filtering NULL explicitly avoids database-specific NULL ordering. Interviewers typically judge tie handling, missing-rank behaviour, and whether you can explain your choice. Window ranking often requires sorting; indexes and execution plans determine actual performance, so do not claim one approach is always fastest.
Key points to remember
- Clarify whether N refers to a distinct salary or an employee row.
- Use DENSE_RANK with descending salary order for consecutive distinct-salary ranks.
- Filter the calculated rank in an outer query or CTE.
- Use DISTINCT for one salary value; retain employee columns to return all tied employees.
- Exclude NULL salaries and define behaviour when the requested rank does not exist.
Common mistakes candidates make
- Using ROW_NUMBER for distinct-salary ranking, which assigns separate positions to tied employees.
- Using RANK without recognising that ties create gaps in subsequent ranks.
- Assuming the query always returns exactly one row, ignoring ties and insufficient distinct salaries.
Likely follow-up questions
How do ROW_NUMBER, RANK, and DENSE_RANK differ?
For salaries 90000, 90000, 75000, and 60000, ROW_NUMBER assigns 1, 2, 3, 4; RANK assigns 1, 1, 3, 4; and DENSE_RANK assigns 1, 1, 2, 3. ROW_NUMBER needs a unique tie-breaker in its ordering if the selected employee must be deterministic.
How would you find the Nth highest salary in each department?
Use DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) in the inner query, excluding NULL salaries. Filter for rank N outside it, returning distinct department-and-salary pairs or all matching employees as required.
Can LIMIT and OFFSET solve this problem?
Yes: select distinct non-NULL salaries, order them descending, and fetch one row after skipping N minus one rows. LIMIT/OFFSET syntax varies across databases, and omitting DISTINCT changes the result to a row position rather than a distinct-salary position.
Interview tip: Start by clarifying distinct salaries versus employee rows, then show DENSE_RANK and explain its behaviour with a small example containing ties.