The second-highest salary: Why the simplest SQL query is often the smartest

Spoiler: That elegant two-line MAX() subquery? It’s not just clever—it’s correct, portable, and often faster than fancier alternatives.

When interviewing for backend or data roles, you’ve probably seen this classic problem: “Find the second highest salary from the Employee table. If there is no second highest salary, return NULL”.

It also appears in LeetCode #176 and similar coding challenge sites.

The solution seems trivial until you consider duplicates, performance, edge cases, and real-world database behavior.

In this deep dive, we’ll compare three common approaches, expose hidden pitfalls, and reveal why the simplest solution is frequently the best, especially when correctness and portability matter.

 

The problem and sample data

We’re given an employee table:

id	name	salary	department_id
1	Ivan	1000.1234	1
2	Anna	1500.1234	1
3	Oleg	2200.1234	2
4	Maria	2200.1234	2
5	David	2000.5678	2

 

Expected result: 2000.5678

Problem statement: Find the second highest distinct salary from the employee table.

Key requirement: Return NULL if fewer than two distinct salaries exist (e.g., all employees earn the same).

This isn’t about the “second row” — it’s about the second highest distinct value.

 

Three common solutions compared

 

METHOD 1: MAX() + Subquery

 

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

 

Why it works:

• Inner query finds the global max.
• Outer query finds the max among values strictly less than that.
• Naturally returns NULL if no such value exists.
• Handles duplicates implicitly—no extra logic needed.

Performance:

Scenario	Time Complexity
No index	O(N)
With index	O(log N + c)

c = small constant (rows scanned after max; usually 1–few)
 

PROS

• Correct by design
• Returns NULL as required
• Works on any SQL database (even SQLite!)

CONS

• Not easily extensible to “3rd highest” or “top 5”

Verdict: Best for this exact problem. Simple, safe, and scalable.

 

METHOD 2: DISTINCT + ORDER BY + OFFSET

 

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

 

Note: Without DISTINCT, this returns the second row, not the second distinct salary. So we must include DISTINCT.

Performance reality:

Database Version	Time Complexity
MySQL ≥ 8.0 / PostgreSQL ≥ 13	O(k log N)
Older versions	O(N)
No index	O(N log N)

 

O(k log N) - only if skip scan (MySQL) or index skip scan (PostgreSQL) is used

 

PROS

• Clean syntax
• Very fast on modern DBs with proper indexes

CONS

• Fails silently on older databases (full scan!)
• Returns empty result, not NULL—your app must handle it
• Requires DESC index for best performance
• DISTINCT can be expensive if skip scan isn’t available

Verdict: Great only if you control your DB version and handle empty results.

 

Method 3: DENSE_RANK() Window Function

 

SELECT salary
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS place
  FROM employee
) AS emp
WHERE place = 2;

 

Flexible?

Note:

• DENSE_RANK() properly handles ties (e.g., two people at rank 1 with the same salary, next is rank 2).
• But returns an empty set, not NULL, so you’d need to wrap in MAX() to comply with requirements, or handle it in the application code.

Performance:

• Always O(N log N) due to sorting, even with an index.
• Most databases compute ranks for all rows, even if you only need rank=2.

Pros:

• Highly flexible (easy to get 3rd, 10th, etc.)
• Handles complex ranking scenarios

Cons:

• Overhead for a simple task
• Doesn’t return NULL natively
• Not supported in older DBs (e.g., MySQL < 8.0)

Verdict: Save this for when you truly need ranking logic, not scalar results.

 

H2H comparison

Method	Correct?	Returns NULL?	Handles Duplicates?	Portable?	Best Use Case
<MAX() + Subquery	Yes	Yes<	Yes	Yes	Second highest (exact match)
DISTINCT + OFFSET	Yes	No	Yes	Modern DBs only	Top-N distinct (with skip scan)
DENSE_RANK()	Yes	No	Yes	Newer DBs	Flexible ranking with ties

 

Key takeaways

1. Favor correctness, clever code can wait
   
   The MAX() subquery satisfies the specification exactly: it returns NULL when appropriate and naturally ignores duplicates.

2. Don’t trust OFFSET for “Nth highest”
   
   Without DISTINCT, it’s wrong. Even with DISTINCT, it’s not portable and may be slow on older systems.

3. Index is not meant for automatic speed
   
   A DESC index helps—but only if your database supports skip scans (MySQL 8.0+ or PostgreSQL 13+). Otherwise, you’re scanning millions of rows for two values.

4. Window functions aren’t always better
   
   They’re powerful, but introduce unnecessary overhead for simple scalar problems.

 

Final recommendation

For the “second-highest salary” problem, use:

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

 

It’s:

• Correct
• NULL-safe
• Portable
• Efficient
• Readable

 

Use DISTINCT, OFFSET, and DENSE_RANK() for problems that truly require their flexibility.

Remember: In SQL, a shorter query doesn’t always run faster, what really matters is writing one that’s clear, accurate, and does exactly what you intend.

 
 

Originally published on my personal blog: https://matevosian.tech/blog/post/database_sql_second_highest_salary