SQL to find the second highest value
Get the second largest value in a column (e.g. the second highest salary).
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);SELECT salary
FROM (
SELECT salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
) ranked
WHERE rnk = 2
LIMIT 1;Change rnk = 2 to any N to get the Nth highest. DENSE_RANK treats equal salaries as the same rank.
How to find the second highest salary
The classic interview question. The most portable answer takes the maximum value that is strictly less than the overall maximum.
How it works
- The subquery finds the overall maximum; the outer MAX finds the largest value below it — that is the second highest.
- This returns NULL if every row has the same salary, which is usually the desired behaviour.
- DENSE_RANK assigns rank 1 to the top value(s), 2 to the next distinct value, and so on, so ties do not skip ranks.
Related SQL queries
- SQL to find the nth highest value
- SQL to number and rank rows
- SQL to get the top n rows per group
- SQL to find duplicate rows
- SQL to delete duplicate rows (keep one)
- SQL to count rows per group
Frequently asked questions
How do I find the second highest salary in SQL?
The classic interview question. The most portable answer takes the maximum value that is strictly less than the overall maximum. The subquery finds the overall maximum; the outer MAX finds the largest value below it — that is the second highest. This returns NULL if every row has the same salary, which is usually the desired behaviour. DENSE_RANK assigns rank 1 to the top value(s), 2 to the next distinct value, and so on, so ties do not skip ranks.
Does this work in PostgreSQL, MySQL, SQL Server, and SQLite?
Yes — this page lists the query for each dialect, since the syntax can differ between database engines.
Can I generate this query for my own tables?
Yes. Describe what you want in plain English with Forgly's free AI SQL Generator and it writes the query for your dialect.