How to Find the Second Highest Salary in SQL (Complete Beginner-Friendly Guide)

If you’ve ever prepared for a SQL interview or worked on a real-world database project, chances are you’ve come across this classic question:

“How do you find the second highest salary in SQL?”

It sounds simple but there are actually several ways to solve it, and each method teaches you something important about how SQL works.

In this guide, we’ll break down every major approach to finding the second highest salary in SQL, explain how each one works, and help you understand when to use which method.

Why Is This Question So Common?

Before we dive into the code, it’s worth asking “why does this question come up so often in interviews?”

Because it tests several important SQL skills at once:

• Your understanding of subqueries
• Your knowledge of aggregate functions
• Your ability to use window functions
• How you handle edge cases like duplicate salaries or NULL values

It’s a small question with a lot of depth. Let’s get into it.

Setting Up the Example Table

For all the examples in this guide, we’ll use a simple employees table:

sql

CREATE TABLE employees (
    id INT,
    name VARCHAR(50),
    salary DECIMAL(10, 2)
);

INSERT INTO employees VALUES
(1, 'Alice', 90000),
(2, 'Bob', 75000),
(3, 'Charlie', 85000),
(4, 'Diana', 75000),
(5, 'Eve', 90000);

Our goal: find the second highest salary from this table.

The highest salary is 90,000. So the correct answer should be 85,000.

Method 1: Using a Subquery (Most Common)

This is the most widely used and beginner-friendly approach.

sql

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

How It Works:

1. The inner query finds the maximum salary — which is 90,000
2. The outer query finds the maximum salary that is less than 90,000 — which gives us 85,000

Result:

second_highest_salary
85000

Pros:

• Easy to understand
• Works in almost every SQL database (MySQL, PostgreSQL, SQL Server, Oracle)

Cons:

• Doesn’t scale well for finding the Nth highest salary without rewriting the query

Method 2: Using LIMIT and OFFSET (MySQL / PostgreSQL)

If you’re using MySQL or PostgreSQL, this is a clean and simple method.

sql

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

How It Works:

1. ORDER BY salary DESC — sorts salaries from highest to lowest
2. DISTINCT — removes duplicate salary values
3. LIMIT 1 OFFSET 1 — skips the first result (highest) and returns the next one

Result:

salary
85000

Pros:

• Very readable and clean
• Easy to modify for Nth highest salary (just change OFFSET)

Cons:

• LIMIT and OFFSET syntax is not supported in SQL Server or Oracle

Method 3: Using DENSE_RANK() Window Function (Best for Complex Cases)

Window functions are a powerful feature in modern SQL. DENSE_RANK() is especially useful when dealing with duplicate values.

sql

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

How It Works:

1. DENSE_RANK() assigns a rank to each salary in descending order
2. Duplicate salaries get the same rank (that’s what makes it “dense”)
3. The outer query filters for rank = 2

Ranking in our example:

salary	rank
90000	1
90000	1
85000	2
75000	3
75000	3

Result:

second_highest_salary
85000

Pros:

• Handles duplicates correctly
• Easy to adapt for any Nth highest salary
• Works in PostgreSQL, SQL Server, Oracle, and MySQL 8+

Cons:

• Slightly more complex for beginners
• Not supported in older versions of MySQL

Method 4: Using a Correlated Subquery

This is a more advanced approach that uses a correlated subquery meaning the inner query references the outer query.

sql

SELECT DISTINCT salary
FROM employees e1
WHERE 1 = (
    SELECT COUNT(DISTINCT salary)
    FROM employees e2
    WHERE e2.salary > e1.salary
);

How It Works:

• For each salary in e1, the inner query counts how many distinct salaries in e2 are greater than it
• When that count equals 1, it means exactly one salary is higher — making this the second highest

Pros:

• Works across most SQL databases
• Good for understanding correlated subqueries

Cons:

• Harder to read
• Can be slow on large datasets

Method	Works On	Handles Duplicates	Beginner Friendly	Best For
Subquery with MAX	All databases	Yes	Yes	Quick, simple queries
LIMIT OFFSET	MySQL, PostgreSQL	Yes (with DISTINCT)	Yes	MySQL/PostgreSQL projects
DENSE_RANK()	MySQL 8+, PostgreSQL, SQL Server, Oracle	Yes	Intermediate	Complex, production queries
Correlated Subquery	All databases	Yes	No	Learning SQL concepts

Handling Edge Cases

What If There Is No Second Highest Salary?

If your table only has one distinct salary, the query should return NULL instead of an error.

The subquery method handles this naturally:

sql

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

If there’s no salary lower than the maximum, the result will be NULL — which is the correct behavior.

What About NULL Salary Values?

Always filter out NULLs to avoid unexpected results:

sql

SELECT MAX(salary) AS second_highest_salary
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees)
AND salary IS NOT NULL;

Finding the Nth Highest Salary

One reason the DENSE_RANK() method is so powerful is that you can easily adapt it to find any Nth highest salary, just change the number in the WHERE clause.

Third highest salary:

sql

SELECT salary
FROM (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
) ranked
WHERE rnk = 3;

With the LIMIT OFFSET method, finding the third highest is just as easy:

sql

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

Real-World Use Cases

You might be wondering, does this actually come up in real projects? Absolutely.

HR Systems — Finding top earners in a department without selecting the highest paid (useful for benchmarking mid-level salaries)

Finance Applications — Identifying the second largest transaction to flag unusual payment patterns

Performance Reports — Ranking employees or products without always focusing on number one

Data Analysis — Exploring salary distributions across teams and roles

Common Mistakes to Avoid

• Not using DISTINCT — duplicate salaries can give you the wrong result
• Using ROW_NUMBER() instead of DENSE_RANK() — ROW_NUMBER treats duplicates as separate rows, which can skip salary values
• Ignoring NULL values — always handle NULLs explicitly in salary columns
• Using LIMIT without DISTINCT — if two employees earn the same top salary, OFFSET 1 might still return that same salary without DISTINCT
• Overcomplicating simple queries — for a basic second highest salary, the subquery method is often all you need

Advantages and Disadvantages of Each Approach

Subquery Method

Advantages: Simple, widely compatible, easy to read Disadvantages: Hard to scale for Nth highest without rewriting

LIMIT OFFSET

Advantages: Clean syntax, easy to modify for any N Disadvantages: Not supported in all databases

DENSE_RANK()

Advantages: Handles duplicates perfectly, highly scalable, production-ready Disadvantages: Requires SQL knowledge of window functions, not supported in older MySQL

Correlated Subquery

Advantages: Works everywhere, great for learning Disadvantages: Slow on large tables, difficult to read

Finding the second highest salary in SQL is one of those questions that seems straightforward on the surface but opens the door to some really important SQL concepts like subqueries, window functions, handling duplicates, and writing scalable queries.

Here’s a quick recap:

• Use the subquery with MAX for simple, quick results
• Use LIMIT OFFSET if you’re on MySQL or PostgreSQL and want clean syntax
• Use DENSE_RANK() for production systems where duplicates matter and scalability is important
• Use the correlated subquery if you want to deepen your understanding of how SQL executes queries

No matter which method you choose, understanding why it works is more valuable than just memorizing the syntax.

FAQs