The most-asked SQL interview question. Know three solutions, and be precise about ties and what to return when there is no Nth value.

Salaries (distinct, highest first): 150000, 120000, 110000, 95000 (Meera and Karan), 85000, 80000, … — so the 4th highest distinct salary is 95000.

1. DENSE_RANK (best general answer)

SQL

WITH ranked AS (
    SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
)
SELECT name, salary FROM ranked WHERE rnk = 4 ORDER BY name;

Output

name  | salary
------+-------
Karan | 95000
Meera | 95000

Returns every employee with that salary. RANK would skip numbers after ties, and ROW_NUMBER would arbitrarily return only one of Meera/Karan and shift everyone below.

2. LIMIT / OFFSET on distinct salaries

SQL

SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 3;          -- N - 1

-- return NULL (not zero rows) when N is too large: wrap it in a scalar subquery
SELECT (SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 99) AS nth_salary;

Output

salary
------
95000

nth_salary
----------
NULL

3. Correlated subquery (no window functions)

SQL

-- the salary that has exactly N-1 distinct salaries above it
SELECT DISTINCT e.salary
FROM employees e
WHERE 3 = (SELECT COUNT(DISTINCT i.salary) FROM employees i WHERE i.salary > e.salary);

Output

salary
------
95000

SQL

-- 2nd highest, the classic MAX-below-MAX trick
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

Output

second_highest
--------------
120000

Nth highest per department

SQL

WITH ranked AS (
    SELECT d.name AS department, e.name, e.salary,
           DENSE_RANK() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS rnk
    FROM employees e JOIN departments d ON d.dept_id = e.dept_id
)
SELECT department, name, salary FROM ranked WHERE rnk = 2 ORDER BY department;

Output

department  | name   | salary
------------+--------+-------
Engineering | Ravi   | 120000
HR          | Vikram | 80000
Sales       | Neha   | 72000

SQL Server / Oracle

SELECT DISTINCT salary FROM employees ORDER BY salary DESC
OFFSET 3 ROWS FETCH NEXT 1 ROWS ONLY;                  -- SQL Server 2012+ / Oracle 12c+

SELECT TOP 1 salary FROM (
    SELECT DISTINCT TOP 4 salary FROM employees ORDER BY salary DESC
) t ORDER BY salary ASC;                               -- older SQL Server

Which approach would you give in an interview?