How would you write a query to find the Nth highest salary?

This is one of the most frequently asked "classic" SQL interview questions, largely because there are several valid approaches with different tie-handling behavior.

Approach 1: DENSE_RANK() — handles ties correctly (recommended)

SELECT DISTINCT salary
FROM (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
) ranked
WHERE rnk = 3;   -- 3rd highest *distinct* salary

DENSE_RANK() assigns the same rank to tied values and doesn't skip ranks afterward — so if two employees are tied for 2nd highest, the next distinct salary is still rank 3 (unlike RANK(), which would skip to rank 4).

Approach 2: OFFSET/FETCH with DISTINCT

SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY;   -- skip top 2, take the next 1 = 3rd highest

(In MySQL/PostgreSQL, the equivalent is LIMIT 1 OFFSET 2.) This works but is less explicit about tie handling, and OFFSET-based pagination degrades in performance on large tables since the engine still has to sort/scan through the skipped rows.

Approach 3: correlated subquery (portable to older engines without window functions)

SELECT DISTINCT salary
FROM employees e1
WHERE 2 = (
    SELECT COUNT(DISTINCT salary) FROM employees e2 WHERE e2.salary > e1.salary
);

This reads as "salary such that exactly 2 distinct salaries are greater than it" — i.e., the 3rd highest.

Why DISTINCT matters in every version

Without DISTINCT (or DENSE_RANK's tie-aware grouping), the "2nd highest salary" query using plain LIMIT 1 OFFSET 1 would return a duplicate of the highest salary if two employees are tied for 1st — a subtle bug that only shows up with tied data, making it worth explicitly discussing tie-handling behavior in an interview rather than assuming any one approach is obviously correct.