Mastering SQL Interviews: Top 5 Commonly Asked Problems and How to Solve Them

Chapter 2: The Second Highest Salary Problem

🧠 Introduction

The “Second Highest Salary” problem is one of the most popular SQL interview questions. It’s a deceptively simple task that tests a candidate’s understanding of sorting, filtering, subqueries, and sometimes window functions.

In this chapter, you will learn:

• Different ways to solve the second highest salary problem
• Performance considerations of each method
• Handling edge cases like duplicate salaries or missing data
• Variations of this question commonly asked in interviews

Let’s begin with a sample table and walk through multiple ways to approach this problem—from the traditional subquery to advanced solutions using ROW_NUMBER() and RANK().

🗃️ Sample Table: Employees

Let’s use the following table:

employees

🔍 Problem Statement

Write a SQL query to find the second highest distinct salary from the employees table.

✅ Approach 1: Subquery with LIMIT and DISTINCT (MySQL-specific)

SELECT DISTINCT salary

FROM employees

ORDER BY salary DESC

LIMIT 1 OFFSET 1;

• DISTINCT ensures we don’t count duplicate salaries.
• LIMIT 1 OFFSET 1 skips the highest and gives the second highest.

✅ Output:

⚠️ This method works well in MySQL, but not in all SQL dialects (e.g., SQL Server).

✅ Approach 2: Subquery with MAX() and < operator (Standard SQL)

SELECT MAX(salary) AS second_highest_salary

FROM employees

WHERE salary < (

    SELECT MAX(salary)

    FROM employees

);

• First, we find the highest salary.
• Then we select the maximum of all salaries less than that highest one.

✅ Output:

✅ Works in almost every SQL database, including PostgreSQL, Oracle, and SQL Server.

✅ Approach 3: Using DENSE_RANK() (Handles Duplicate Salaries)

WITH ranked_salaries AS (

  SELECT salary,

         DENSE_RANK() OVER (ORDER BY salary DESC) AS rank

  FROM employees

)

SELECT salary

FROM ranked_salaries

WHERE rank = 2;

• DENSE_RANK() gives the same rank to duplicate salaries.
• We fetch only those where rank = 2.

⚠️ Best choice when you want to return all employees with the second highest salary.

🔄 Variation: Include employee details

WITH ranked_employees AS (

  SELECT *,

         DENSE_RANK() OVER (ORDER BY salary DESC) AS rank

  FROM employees

)

SELECT employee_id, name, salary

FROM ranked_employees

WHERE rank = 2;

🔁 Bonus: Handling Ties with RANK() vs ROW_NUMBER()

Example with RANK():

WITH ranked AS (

  SELECT name, salary,

         RANK() OVER (ORDER BY salary DESC) AS rnk

  FROM employees

)

SELECT name, salary

FROM ranked

WHERE rnk = 2;

💡 Edge Cases

1. Only one unique salary exists
   → All methods return NULL, which is correct behavior.
2. Multiple people with the same second-highest salary
   → Use RANK() or DENSE_RANK() to capture all.
3. Negative or zero salaries
   → These will still be considered in ordering unless filtered out.
4. NULL salaries
   → By default, NULLs are excluded from aggregates like MAX().

⚙️ Performance Tips

• Indexing the salary column speeds up sorting and filtering.
• Avoid using DISTINCT unless necessary — it adds overhead.
• If you query second-highest salaries frequently, consider creating a materialized view in large systems.

💼 Real-World Applications

• Show the second highest sale in a given month.
• Identify the second best-performing product.
• Find the runner-up in a contest.

🧑‍💻 Practice Challenge

Given a table players (player_id, name, score), write a query to find all players with the second highest score, including ties.

Bonus: Modify it to return the third highest score.

✅ Summary

• We explored multiple ways to find the second highest salary.
• The MAX(<) method is universally compatible.
• DENSE_RANK() and RANK() are great for tie handling.
• Always watch for duplicates, NULLs, and database compatibility.