Second Highest Salary in SQL: 4 Ways, and Which Ones Quietly Break

"Write a query to find the second highest salary."

If you have prepared for any SQL round, you have seen this one. And you probably memorised an answer.

Here's the thing. Most memorised answers work on the sample table in the tutorial and break on the interviewer's follow-up. Two things trip people up: ties (two people with the same top salary) and no second row (a department with one employee).

So let's do it properly. Same small table, four solutions, and we'll see which ones survive.

The data

I ran everything below in SQLite 3.53. The syntax is standard enough that it works in PostgreSQL and MySQL 8 too, with a couple of small notes where it differs.

CREATE TABLE employees(id INTEGER, name TEXT, dept TEXT, salary INTEGER); INSERT INTO employees VALUES (1,'Asha','Eng',90000), (2,'Ravi','Eng',90000), (3,'Meera','Eng',75000), (4,'Kiran','Sales',60000), (5,'Neha','Sales',55000), (6,'Tara','HR',50000); 

Notice what I did on purpose. Asha and Ravi both earn 90000, so the top salary is tied. Tara is alone in HR.

The correct answer for the whole company is 75000. Keep that in mind.

Way 1: ORDER BY with LIMIT and OFFSET

This is the one most people write first.

SELECT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1; 

Output:

90000 

Wrong. It sorted the rows 90000, 90000, 75000... and skipped one row, which was the first 90000. The second row is the other 90000.

The fix is DISTINCT, so duplicates collapse before you skip:

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

Output:

75000 

Correct now. In SQL Server the same idea is OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY.

Way 2: Max of everything below the max

No sorting, no window functions. Just ask: what is the biggest salary that is smaller than the biggest salary?

SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees); 

Output:

75000 

This handles ties without any extra work, because salary < max throws away every row tied at the top. It's also easy to explain out loud, which matters in an interview.

The catch is that it only gives you the second highest. If the interviewer then says "okay, now the 5th highest", you have to nest it again and again. Not nice.

Way 3: DENSE_RANK

This is the version that scales to any N.

SELECT DISTINCT salary FROM ( SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees ) t WHERE rnk = 2; 

Output:

75000 

Want the 5th highest? Change rnk = 2 to rnk = 5. That's it.

Why DENSE_RANK and not RANK or ROW_NUMBER? Let's look at all three on our data.

I ran ROW_NUMBER() and RANK() on this table to get those numbers. (ROW_NUMBER's order between Asha and Ravi can swap, since they tie.)

• ROW_NUMBER never ties, so "rank 2" is just the other 90000. Wrong.
• RANK ties, but then skips. After two people at rank 1, the next rank is 3. There is no rank 2 at all, so you get an empty result.
• DENSE_RANK ties and doesn't skip. Meera is rank 2. Correct.

Rule of thumb: for "Nth highest value", use DENSE_RANK.

Dropping the DISTINCT is also useful: if you want the people earning the second highest salary, just select name too:

SELECT name, salary FROM ( SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees ) t WHERE rnk = 2; 

Output:

Meera|75000 

Way 4: The correlated subquery

You'll see this in older tutorials and some interviews, because it works without window functions.

SELECT name, salary FROM employees e WHERE 2 = ( SELECT COUNT(DISTINCT salary) FROM employees WHERE salary >= e.salary ); 

Output:

Meera|75000 

Read it like this: for each employee, count how many distinct salaries are at least as high as theirs. If that count is 2, they are the second highest.

It's correct, and it's also slow on big tables, because the inner query runs once per row. Good to understand, not what I'd write today if window functions are available.

The trap everyone misses: what if there is no second salary?

Our HR department has only Tara. Ask for the second highest salary in HR and see what each method returns.

Way 1 with LIMIT/OFFSET:

SELECT salary FROM ( SELECT salary FROM employees WHERE dept = 'HR' ORDER BY salary DESC LIMIT 1 OFFSET 1 ) t; 

Output: no rows at all.

Way 2 with MAX:

SELECT MAX(salary) FROM employees WHERE dept = 'HR' AND salary < (SELECT MAX(salary) FROM employees WHERE dept = 'HR'); 

Output: one row containing NULL.

These look similar but behave differently. An empty result and a NULL are not the same thing to the application reading your query. Some interviewers specifically ask for "return NULL if there is no second highest salary". If so, wrap the Way 1 query in a subquery in the SELECT list, which turns "no rows" into NULL:

SELECT ( SELECT DISTINCT salary FROM employees WHERE dept = 'HR' ORDER BY salary DESC LIMIT 1 OFFSET 1 ) AS second_highest; 

Output:

NULL 

Good to know before you walk into the room, not after.

Second highest per department

The follow-up that comes right after is almost always "now do it for each department". This is where DENSE_RANK shines, because you only add PARTITION BY:

SELECT dept, name, salary FROM ( SELECT name, dept, salary, DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk FROM employees ) t WHERE rnk = 2; 

Output:

Eng|Meera|75000 Sales|Neha|55000 

HR doesn't appear because it has no rank 2, which is the right behaviour here. Asha and Ravi both being rank 1 in Eng doesn't push Meera out of rank 2.

What I'd say in the interview

1. "I'd use DENSE_RANK so ties don't break it, and it works for any N."
2. "If window functions aren't allowed, I'd use MAX with a subquery on salary < MAX(salary)."
3. "And I'd ask whether they want NULL or an empty result when there is no second salary."

That third line is what sounds like experience. Asking about the edge case before the interviewer does is a good habit in any SQL question.

Try it yourself

If you want to practise a few more like this, I've put together SQL problems on LakeBench, a platform I'm building. "Top three salaries" is free and is a good warm-up for the ranking idea: lakebench.in/studio/lb-sql-05. The exact "Second highest salary" problem is lb-sql-20. There's also a page of window function problems at akebench.in/learn/sql-warehousing/window-functions.

Which of the four would you have written first? I'd bet on Way 1.