Schema and sample data
| emp_id | name | salary |
|---|---|---|
| 1 | Ava Torres | 120000.00 |
| 2 | Ben Ncube | 120000.00 |
| 3 | Cara Singh | 95000.00 |
| 4 | Deng Liu | 88000.00 |
| 5 | Emma Rossi | 88000.00 |
| 6 | Finn Walsh | 72000.00 |
Show setup SQL (copy to run)
CREATE TABLE Employees (
emp_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
salary DECIMAL(10,2) NOT NULL
);
INSERT INTO Employees (emp_id, name, salary) VALUES
(1, 'Ava Torres', 120000.00),
(2, 'Ben Ncube', 120000.00),
(3, 'Cara Singh', 95000.00),
(4, 'Deng Liu', 88000.00),
(5, 'Emma Rossi', 88000.00),
(6, 'Finn Walsh', 72000.00);From the Employees table, return the second highest distinct salary as a single value in a column named second_highest_salary. "Distinct" matters here: two people share the top salary, but that top figure still counts as one salary, so the answer is the next distinct amount below it.
Expected result
| second_highest_salary |
|---|
| 95000.00 |
Show hint
Rank the distinct salaries from highest to lowest, then pick the one sitting in position 2. Because two employees tie for the top salary, a ranking that leaves gaps (like RANK or ROW_NUMBER over raw rows) would push the real answer out of slot 2. You want a ranking that treats equal salaries as one rank and does not skip numbers.
Try to write the query yourself before you open this.
Show solution and explanation
SELECT DISTINCT salary AS second_highest_salary
FROM (
SELECT salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM Employees
) ranked
WHERE salary_rank = 2;DENSE_RANK is the reliable primary tool here. Ordering by salary DESC, it assigns rank 1 to the top salary (120000, shared by Ava and Ben, who both get rank 1), rank 2 to the next distinct salary (95000), rank 3 to 88000, and so on. Unlike RANK, it does not skip numbers after a tie, so the second distinct salary always lands on rank 2 no matter how many people tie at the top. Filtering WHERE salary_rank = 2 and taking DISTINCT salary returns exactly one value: 95000.00.
A classic subquery alternative avoids window functions entirely: SELECT MAX(salary) FROM Employees WHERE salary < (SELECT MAX(salary) FROM Employees). The inner query finds the top salary (120000), and the outer MAX grabs the largest salary strictly below it (95000). This is compact and works on older engines, but it only generalizes to the "third" or "fourth" highest with awkward nesting.
A third option uses paging: SELECT DISTINCT salary FROM Employees ORDER BY salary DESC OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY. The DISTINCT collapses the tied top salaries into one entry, OFFSET 1 ROWS skips the highest, and FETCH NEXT 1 ROWS ONLY returns the next one. The common mistake across all three approaches is forgetting the distinct step: if you rank raw rows or skip without deduplicating, the tie at the top makes 120000 appear twice and you accidentally return 120000 as the "second" salary. For more variations, see the second highest salary scenario walkthrough.