Second Highest Salary in SQL: 5 Tested Queries and the Tie Trap
Finding the second highest salary in SQL is the most famous interview query, and most answers break on ties. We ran five methods on the same table, including duplicate top salaries, NULLs and a table with one salary, and show which ones return the right answer.
- When two people share the top salary, the second highest salary is the next distinct value. In our table that is 9000, not 12000.
ORDER BY salary DESC LIMIT 1 OFFSET 1withoutDISTINCTreturned 12000, the wrong answer.DENSE_RANK() = 2returned the correct salary and both employees who earn it.RANK() = 2returned no rows at all.- Wrap the query as a scalar subquery to get
NULLinstead of an empty result when there is no second salary.
“Write a query to find the second highest salary” appears in SQL interviews for developers, testers and data analysts alike. It looks trivial, and that is the point: the interviewer wants to see whether you think about duplicate values, missing data and empty results. A query that works on a neat sample table can quietly return the wrong number on real data.
We ran every query below on SQLite 3.50.4. The ideas carry over to every database, and we list the syntax changes for MySQL, PostgreSQL, SQL Server and Oracle near the end. For more practice questions, see our SQL interview questions for testers.
What is actually being asked
Before writing SQL, settle two questions, ideally out loud in an interview:
- Distinct value or second row? If two people earn the top salary, is the answer the next lower amount, or that same top amount again? Almost always, the intended answer is the second highest distinct salary.
- Salary only, or the people too? Returning just the number is simpler; returning every employee who earns it needs a different query.
The test table, with a tie on purpose
CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INT);
INSERT INTO employees VALUES
(1,'Asha','Eng',9000), (2,'Ben','Eng',12000), (3,'Chen','QA',12000),
(4,'Dina','QA',7000), (5,'Eli','Eng',9000), (6,'Fay','Ops',6000);
Ben and Chen share the top salary of 12000. Asha and Eli share 9000. So the distinct salaries in order are 12000, 9000, 7000, 6000, and the correct second highest salary is 9000.
Method 1: MAX with a subquery
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
second_highest
9000
The inner query finds 12000. The outer query takes the maximum of everything strictly below it. Because it compares values, not rows, ties at the top cannot fool it. It also works on any SQL database, even very old ones without window functions, which makes it the safest default answer. Its weakness is that it only generalises to the third or fourth highest by nesting more subqueries, which gets ugly fast. Our SQL subqueries guide explains how the nesting is evaluated.
Method 2: ORDER BY with LIMIT and OFFSET
The popular short answer sorts salaries and skips the first one:
SELECT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;
salary
12000
Wrong. The first row is Ben at 12000 and the second row is Chen, also at 12000. The query skipped a row, not a value. Adding DISTINCT fixes it:
SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;
salary
9000
With DISTINCT, this is short, readable and easy to change to the Nth highest by changing the offset to N minus 1. It is a good answer as long as you say why the DISTINCT is there.
Tip: in an interview, run your query in your head against a table with a tie at the top. It is the test case the interviewer is waiting for, and it is the same habit as boundary value analysis in testing.
Method 3: DENSE_RANK
Window functions give the most flexible answer. DENSE_RANK gives equal salaries the same rank and leaves no gaps, so rank 2 is always the second distinct salary:
SELECT name, salary FROM (
SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS dr
FROM employees
) AS ranked
WHERE dr = 2;
name | salary
Asha | 9000
Eli | 9000
This returns the people as well as the amount, and both employees who earn 9000 appear. Add DISTINCT and select only salary if you just want the number. It needs window function support: MySQL 8.0 or later, SQLite 3.25 or later, and any current PostgreSQL, SQL Server or Oracle.
Why RANK and ROW_NUMBER give the wrong answer
We swapped in the other two numbering functions and kept everything else the same:
-- RANK() ... WHERE r = 2
name | salary
(no rows)
-- ROW_NUMBER() ... WHERE rn = 2
name | salary
Chen | 12000
RANK gave both 12000 earners rank 1 and then skipped to rank 3, so no row has rank 2. ROW_NUMBER gave the tied 12000 earners numbers 1 and 2, so it returned a top earner. Both are classic interview mistakes. Our guides to RANK vs DENSE_RANK and SQL ROW_NUMBER show the full numbering side by side.
Method 4: correlated subquery
A pre-window-function trick counts how many distinct salaries are higher than each one:
SELECT DISTINCT salary FROM employees e1
WHERE 1 = (SELECT COUNT(DISTINCT salary) FROM employees e2
WHERE e2.salary > e1.salary);
salary
9000
A salary is the second highest when exactly one distinct salary is above it. Replace 1 with N - 1 for the Nth highest. It is correct and portable, but the inner query runs once per row, so it can be slow on large tables. It is mainly worth knowing because interviewers sometimes ask for a solution without LIMIT or window functions.
Method 5: second highest salary per department
The common follow-up question. Add PARTITION BY so ranking restarts in each department:
SELECT dept, name, salary FROM (
SELECT dept, name, salary,
DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS dr
FROM employees
) AS ranked
WHERE dr = 2;
dept | name | salary
Eng | Asha | 9000
Eng | Eli | 9000
QA | Dina | 7000
Ops has only one employee, so it has no second highest salary and does not appear. Say that out loud: if the business wants Ops listed with an empty value, you would need a LEFT JOIN from a department list.
Generalising to the Nth highest salary
Any of the scalable methods change with one number. For the third highest:
SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 2;
salary
7000
With DENSE_RANK, use WHERE dr = 3; with the correlated subquery, use WHERE 2 = (...). The nested MAX approach is the only one that does not generalise cleanly.
Edge cases we tested
Only one distinct salary
We made a table where both employees earn 5000:
-- MAX subquery
second_highest
NULL
-- LIMIT 1 OFFSET 1 without DISTINCT
salary
5000
-- scalar subquery with DISTINCT
SELECT (SELECT DISTINCT salary FROM solo ORDER BY salary DESC LIMIT 1 OFFSET 1) AS second_highest;
second_highest
NULL
The MAX method returns NULL because MAX of no rows is NULL. The plain LIMIT query wrongly returns 5000. A bare DISTINCT ... LIMIT ... OFFSET query returns an empty result, which some applications and test harnesses handle badly; wrapping it in an outer SELECT ( ... ) turns the empty result into a single NULL row. LeetCode’s version of this problem expects exactly that NULL.
NULL salaries
We added an employee with a NULL salary. The descending DISTINCT query still returned 9000, because SQLite sorts NULL first in ascending order and last in descending order. PostgreSQL and Oracle do the opposite and sort NULL first in descending order, so there NULL could be returned as the highest value. Add WHERE salary IS NOT NULL to be safe on every database. MAX and DENSE_RANK ignore or isolate NULL differently, which our SQL NULL values guide covers in detail.
Syntax for other databases
| Database | Skip one distinct salary |
|---|---|
| MySQL, PostgreSQL, SQLite | SELECT DISTINCT salary ... ORDER BY salary DESC LIMIT 1 OFFSET 1 |
| SQL Server 2012+, Oracle 12c+, PostgreSQL | ... ORDER BY salary DESC OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY |
| SQL Server (older) | SELECT TOP 1 salary FROM (SELECT DISTINCT TOP 2 salary FROM employees ORDER BY salary DESC) AS t ORDER BY salary ASC |
| All of them | The MAX subquery and DENSE_RANK versions work unchanged |
We only ran the SQLite versions; the other rows are standard syntax from each database’s documentation and were not executed here.
Which method should you use?
| Method | Handles ties | Nth highest | Returns names | Portable |
|---|---|---|---|---|
| MAX subquery | Yes | Awkward | No | Everywhere |
| LIMIT OFFSET | Only with DISTINCT | Easy | No | Syntax varies |
| DENSE_RANK | Yes | Easy | Yes | Needs window functions |
| Correlated subquery | Yes | Easy | Can | Everywhere, slower |
In real code, use DENSE_RANK: it handles ties, scales to N, works per group and returns full rows. In an interview, give the MAX subquery first as the safe answer, then show DENSE_RANK and explain the tie behaviour.
How to answer in an interview
- Ask whether ties should count once. State your assumption if there is no answer.
- Write the MAX subquery, because it is correct and short.
- Offer DENSE_RANK for the Nth highest or per-department versions.
- Mention the edge cases: one salary,
NULLvalues and empty results. - Mention an index on
salaryfor large tables.
Final thoughts
The second highest salary in SQL is a small query that tests big habits: define the requirement, think about ties, and check empty and NULL cases. In our tests the MAX subquery, DISTINCT with LIMIT OFFSET, DENSE_RANK and the correlated subquery all returned the right answer, while plain LIMIT OFFSET, RANK and ROW_NUMBER failed on the tie. Remember which is which and you will handle every version of this question.
Frequently asked questions
What is the simplest query for the second highest salary in SQL?
SELECT MAX(salary) FROM employees WHERE salary is less than the result of SELECT MAX(salary) FROM employees. It handles ties and works on every SQL database.
Why does LIMIT 1 OFFSET 1 sometimes return the highest salary?
If two employees share the top salary, the second row still holds the top value. Add DISTINCT so the query skips a value rather than a row.
Should I use RANK or DENSE_RANK for the second highest salary?
Use DENSE_RANK. RANK skips numbers after a tie, so with two top earners no row gets rank 2 and the query returns nothing.
How do I find the Nth highest salary?
Use DENSE_RANK and filter on rank N, or use SELECT DISTINCT salary ordered descending with LIMIT 1 OFFSET N minus 1.
What should the query return if there is no second highest salary?
Usually NULL. The MAX subquery returns NULL naturally, and wrapping a LIMIT query in an outer SELECT turns an empty result into a single NULL row.
Keep learning
Need help with testing, WordPress or SEO?
We help teams ship better software and grow their sites. Tell us what you are working on and we will suggest a clear next step.