Schema and sample data
| emp_id | name | department | salary |
|---|---|---|---|
| 1 | Ava Torres | Engineering | 95000.00 |
| 2 | Ben Ncube | Sales | 85000.00 |
| 3 | Cara Singh | Engineering | 85000.00 |
| 4 | Deng Liu | Support | 72000.00 |
| 5 | Emma Rossi | Sales | 72000.00 |
| 6 | Farah Khan | Engineering | 61000.00 |
| 7 | Gino Bianchi | Support | 50000.00 |
Show setup SQL (copy to run)
CREATE TABLE Employees (
emp_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
department VARCHAR(30),
salary DECIMAL(10,2) NOT NULL
);
INSERT INTO Employees (emp_id, name, department, salary) VALUES
(1, 'Ava Torres', 'Engineering', 95000.00),
(2, 'Ben Ncube', 'Sales', 85000.00),
(3, 'Cara Singh', 'Engineering', 85000.00),
(4, 'Deng Liu', 'Support', 72000.00),
(5, 'Emma Rossi', 'Sales', 72000.00),
(6, 'Farah Khan', 'Engineering', 61000.00),
(7, 'Gino Bianchi', 'Support', 50000.00);From the Employees table, return the 3rd highest distinct salary. Salaries can tie, and tied salaries count as a single position, so two people earning the same amount do not use up two ranks. Return one row with a single column named nth_highest_salary. If there are fewer than 3 distinct salaries, the query should return no rows.
Expected result
| nth_highest_salary |
|---|
| 72000.00 |
Show hint
Because tied salaries must share a single position, you want a ranking function that assigns the same rank to ties and does not leave gaps. DENSE_RANK() over the salaries in descending order gives every distinct salary its own number: the top salary is 1, the next distinct salary is 2, and so on. Wrap that in a CTE and keep only the rows where the rank equals 3.
Try to write the query yourself before you open this.
Show solution and explanation
WITH RankedSalaries AS (
SELECT
salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM Employees
)
SELECT DISTINCT salary AS nth_highest_salary
FROM RankedSalaries
WHERE salary_rank = 3;DENSE_RANK() OVER (ORDER BY salary DESC) numbers the salaries from the top down while giving equal salaries the same rank and never skipping a number. On this data the distinct salaries are 95000, 85000, 72000, 61000, and 50000, so they receive ranks 1, 2, 3, 4, and 5. Both employees on 85000 (Ben and Cara) share rank 2, and both on 72000 (Deng and Emma) share rank 3 - the ties do not consume extra positions. Filtering salary_rank = 3 and taking the DISTINCT salary leaves a single value: 72000.00. See DENSE_RANK for how it differs from RANK() and ROW_NUMBER().
The choice of ranking function matters. RANK() would leave gaps after a tie (the salary after the two 85000 rows would jump to rank 4, not 3), and ROW_NUMBER() would give tied salaries different numbers, so neither answers the "distinct salary" question correctly. That is the most common mistake on this problem. A set-based alternative avoids window functions entirely: SELECT DISTINCT salary FROM Employees ORDER BY salary DESC OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY; - the offset of N minus 1 skips the two higher distinct salaries and fetches the third. To generalize to N, swap 2 for N minus 1.
Watch the fewer-than-N-distinct edge case: if the table held only two distinct salaries, no row would ever get salary_rank = 3, so the query returns an empty result set rather than a wrong or NULL answer - exactly the behavior the prompt asks for. To find the Nth highest salary within each department instead of overall, add a partition: DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC), which restarts the ranking for every department. This pattern shows up constantly in interviews; see the Nth highest salary scenario question for more variations.