How to Find the Second Highest Salary in SQL (5 Ways)

“Find the second highest salary” is probably the most asked SQL interview question there is, and it is asked for a good reason: there is no single keyword for it. You have to combine things you already know, and the way you combine them says something about how well you understand SQL.

This page shows five approaches that all work, explains what each one actually does, and then answers the two follow-up questions that separate a memorised answer from an understood one: what happens with ties, and how to generalise to the Nth highest. Every query below runs against this site’s sample Employees table — press Run on any of them and change the numbers.

The data we are working with

Twelve employees, each with a salary. Here they are sorted from highest to lowest, so you can check every answer below by eye:

SELECT first_name, last_name, salary
FROM Employees
ORDER BY salary DESC;

The highest salary is the first row. The second highest is the second row. Now let us get there with SQL instead of with our eyes.

Method 1: the subquery everyone reaches for first

The idea in one sentence: find the largest salary that is smaller than the largest salary.

SELECT MAX(salary) AS second_highest
FROM Employees
WHERE salary < (SELECT MAX(salary) FROM Employees);

The inner query runs first and collapses to a single number — the top salary. The outer query then ignores everybody on that number and takes the maximum of what is left. Two passes over the table, no sorting, and it works in every SQL database.

This is the answer to give when someone says “without using LIMIT or window functions”, which is a common way to make the question harder.

Method 2: ORDER BY with LIMIT and OFFSET

Sort descending, skip one row, take one row:

SELECT first_name, salary
FROM Employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

Read LIMIT 1 OFFSET 1 as “skip 1, then give me 1”. It is the shortest answer and the easiest to adapt: OFFSET 2 gives the third highest, OFFSET 4 the fifth.

It also has the biggest trap on this page. It returns the second highest row, not the second highest salary. If two people earn the top amount, the row you get back is the second of those two — still the top salary. Add DISTINCT when you want the second distinct value:

SELECT DISTINCT salary
FROM Employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

One more thing worth knowing for interviews: this syntax is not universal. SQL Server writes OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY, and Oracle before 12c used a ROWNUM wrapper. The dialect differences page lists the row-limiting syntax for each database side by side.

Method 3: count how many salaries are above you

This one looks strange the first time and then never leaves your toolbox. For each salary, count the distinct salaries greater than or equal to it. The second highest salary is the one where that count is exactly 2:

SELECT DISTINCT e.salary
FROM Employees e
WHERE 2 = (
    SELECT COUNT(DISTINCT x.salary)
    FROM Employees x
    WHERE x.salary >= e.salary
);

The inner query mentions e.salary from the outer query, which makes it a correlated subquery: it is evaluated once per row rather than once in total. Slow on a large table, but it generalises perfectly — change 2 to 5 and you have the fifth highest, with ties handled the way you usually want because of the COUNT(DISTINCT ...).

Method 4: a window function (the modern answer)

If the interview allows window functions, this is the answer that shows you know the language as it is today. DENSE_RANK() numbers the salaries from the top, giving equal salaries the same number and leaving no gaps:

WITH ranked AS (
  SELECT first_name, salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
  FROM Employees
)
SELECT first_name, salary
FROM ranked
WHERE salary_rank = 2;

Two details that get asked about immediately. First, the ranking has to happen in a CTE or subquery, because WHERE runs before window functions are computed — you cannot write WHERE salary_rank = 2 in the same SELECT that creates it. Second, the choice of ranking function matters, and this data cannot show you why: no two employees earn the same, so every second-highest query above agrees on 142000. Create a tie first — give Ananya the same salary as Chen — and compare the three numbering functions:

UPDATE Employees SET salary = 156000 WHERE emp_id = 1;

SELECT first_name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC, first_name) AS row_num,
       RANK()       OVER (ORDER BY salary DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM Employees
ORDER BY salary DESC, first_name
LIMIT 4;

Read the three columns as three answers to “who is number 2?”. ROW_NUMBER() says Chen, who is still on the top salary; that is the same salary LIMIT 1 OFFSET 1 without DISTINCT would give you. RANK() jumps from 1 to 3, so WHERE salary_rank = 2 would match nothing at all. DENSE_RANK() gives 2 to Tomas at 131000, the second highest salary.

That is the real content of the question. DENSE_RANK() is the right default for “second highest salary”; RANK() silently returns zero rows when the top value is tied, which is a bug waiting to happen. The window functions guide works through all three.

The UPDATE changes only the copy of the data in your browser. In the playground, the Reset button or a reload puts Ananya’s salary back.

Method 5: the Nth highest, once and for all

Every method above is a special case of the same question. Here is each one written so that changing a single number gives you any position you like:

The third highest, with LIMIT and OFFSET:

SELECT DISTINCT salary
FROM Employees
ORDER BY salary DESC
LIMIT 1 OFFSET 2;

And the version that survives a follow-up question about ties, with the count changed to 3:

SELECT DISTINCT e.salary
FROM Employees e
WHERE 3 = (
    SELECT COUNT(DISTINCT x.salary)
    FROM Employees x
    WHERE x.salary >= e.salary
);

The second highest salary per department

This is the follow-up you should expect, and it is where window functions stop being optional. Partition the ranking by department and the same query answers it for every department at once:

WITH ranked AS (
  SELECT d.dept_name, e.first_name, e.salary,
         DENSE_RANK() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS salary_rank
  FROM Employees e
  JOIN Departments d ON d.dept_id = e.dept_id
)
SELECT dept_name, first_name, salary
FROM ranked
WHERE salary_rank = 2
ORDER BY dept_name;

PARTITION BY restarts the numbering for each department, so rank 2 means “second highest within this department”. Three departments come back, not five: Marketing has only one employee, so it has no second highest salary, and Support has no employees at all. Say that out loud in an interview — a department with no rank 2 silently disappears from the result, and the person asking may expect a row with NULL instead.

Without window functions you would use a correlated subquery that compares each salary only with others in the same department, which is the honest answer to give if the interviewer says the database is old.

Which answer should you give?

• If they say “any way you like”: the LIMIT/OFFSET version with DISTINCT. It is short and obviously correct, and you can explain it in one sentence.
• If they say “no LIMIT”: the nested MAX. Two lines, works everywhere, handles ties.
• If they say “now the Nth” or “per department”: DENSE_RANK in a CTE. Say out loud why the rank has to be computed in a subquery.
• If they say “what about ties?”: this is the real question. Explain that “second highest salary” and “the person with the second highest salary” are different questions, and that DISTINCT or DENSE_RANK answers the first while plain OFFSET answers the second.

The best answer in an interview is usually two methods and the trade-off between them, not one method delivered fast.

Practice this topic

Reading explains the idea; producing it yourself is what makes it stick. These exercises use exactly what this page covers, and your answer is checked by running it against the same sample database:

• Better paid than averageMedium
• Cheapest and dearestEasy
• Big spendersHard
• Well-paid departmentsHard

See all 42 exercises →

Frequently asked questions

Related reading

• SQL window functions: ROW_NUMBER, RANK and PARTITION BY
• SQL subqueries explained
• ORDER BY and LIMIT: sorting, top-N and pagination
• 25 SQL interview questions and answers
• Row limiting in PostgreSQL, MySQL, SQL Server and SQLite