Sql Query To Find Second Highest Salary

SQL Query to Find the Second Highest Salary

When working with employee data, it is common to need the second highest salary for reporting, analysis, or salary benchmarking. On top of that, whether you are using MySQL, PostgreSQL, SQL Server, Oracle, or any other relational database, the core logic remains the same: you must exclude the top salary and then select the maximum value from the remaining rows. Below is a thorough look that covers the underlying principles, multiple query approaches, and best practices for handling edge cases such as duplicate salaries.

No fluff here — just what actually works.

Introduction

Finding the second highest salary is a classic SQL interview question and a practical task in real‑world data processing. The challenge lies in correctly handling ties, nulls, and varying database dialects. This article walks you through the step‑by‑step reasoning, provides ready‑to‑use SQL statements, and explains how to adapt them for different database systems. By the end, you will understand not only how to write the query but also why each part works, enabling you to troubleshoot similar ranking problems.

Understanding the Core Logic

At its simplest, the second highest salary can be defined as:

The maximum salary value that is less than the maximum salary in the table That's the part that actually makes a difference..

This definition works when salaries are unique. When duplicates exist, the definition must be refined: you want the highest salary that is strictly less than the overall maximum, ignoring any rows that share the top salary.

Key Concepts

• MAX() – an aggregate function returning the greatest value in a column.
• DISTINCT – eliminates duplicate rows, useful when you need a list of unique salary values.
• ORDER BY – sorts rows, often used to limit results.
• LIMIT / OFFSET – pagination clauses that work in MySQL, PostgreSQL, SQLite, etc.
• RANK() / DENSE_RANK() – window functions that assign rank numbers based on salary values.

Basic Query Using MAX() and a Subquery

The most straightforward approach uses a subquery to first find the highest salary, then selects the maximum salary that is lower than that value.

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

Explanation

1. The inner query (SELECT MAX(salary) FROM employees) returns the top salary.
2. The outer query selects the maximum salary from all rows where salary is less than that top value.
3. If no such row exists (e.g., only one employee or all salaries are identical), the result is NULL.

Handling Duplicate Top Salaries

If multiple employees earn the highest salary, the simple query above still works because it filters out all rows with that salary, leaving the next distinct value.

Example:

Running the basic query returns 8000, correctly identifying Carol’s salary as the second highest The details matter here..

Using ORDER BY and LIMIT (MySQL, PostgreSQL, SQLite)

Another common pattern is to sort salaries in descending order and skip the first row.

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

Why it works

• ORDER BY salary DESC places the highest salary first.
• LIMIT 1 OFFSET 1 discards the first row (the highest) and returns the next row.
• This method returns only one row, even if there are duplicates of the second highest salary. To capture all ties, you can use a subquery to find the second distinct salary value and then select all rows matching it.

Using Window Functions (SQL Server, Oracle, PostgreSQL)

Window functions provide a clean way to rank salaries and then filter by rank. Two popular functions are RANK() and DENSE_RANK().

DENSE_RANK() – No Gaps in Ranking

WITH RankedSalaries AS (
    SELECT
        employee_id,
        name,
        salary,
        DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
    FROM employees
)
SELECT employee_id, name, salary
FROM RankedSalaries
WHERE salary_rank = 2;

Explanation

• DENSE_RANK() assigns rank 1 to the highest salary, rank 2 to the next distinct salary, and so on, without leaving gaps when ties occur.
• The CTE (RankedSalaries) computes the rank for each row.
• The outer query filters for salary_rank = 2, returning all employees whose salary is the second highest.

RANK() – Gaps When Ties Occur

WITH RankedSalaries AS (
    SELECT
        employee_id,
        name,
        salary,
        RANK() OVER (ORDER BY salary DESC) AS salary_rank
    FROM employees
)
SELECT employee_id, name, salary
FROM RankedSalaries
WHERE salary_rank = 2;

• RANK() also gives rank 1 to the top salary, but if two employees share the top salary, they both receive rank 1 and the next salary gets rank 3, creating a gap. This can be useful if you need to know how many people are ahead of the second highest earner.

Handling NULL Values

SQL treats NULL as unknown, and aggregate functions like MAX() ignore NULLs. Even so, when filtering with < or >, NULL comparisons yield unknown and exclude rows. To be safe, you can add a condition AND salary IS NOT NULL.

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

Edge Cases and Best Practices

1. Single Employee or All Salaries Equal
   If the table contains only one employee or every employee has the same salary, the query returns NULL. This is expected behavior; you may want to handle it in your application layer.

2. Large Tables
   Using ORDER BY salary DESC LIMIT 1 OFFSET 1 can be inefficient on huge datasets because the database must sort the entire table. If performance is critical, consider creating an index on the salary column and using the MAX()‑subquery approach, which often leverages the index.

3. Multiple Columns Needed
   When you need more than just the salary amount (e.g., employee name, department), use the ranking CTE approach and filter by rank, as shown earlier.

4. Database Dialect Differences

   • MySQL: Supports LIMIT and OFFSET. The DENSE_RANK() window function is available from MySQL 8.0.
   • PostgreSQL: Supports both LIMIT/OFFSET and window functions.
   • SQL Server: Uses TOP/OFFSET FETCH and RANK()/DENSE_RANK().
   • Oracle: Uses FETCH FIRST (12c+) and window functions.

5. Performance Tip
   If you frequently query the second highest salary, materialize it in a view or a derived table:

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

   This view can be referenced like any other table, reducing query time.

Frequently Asked Questions

Q: What if I need the second highest distinct salary?
A: Use the MAX()‑subquery method, which inherently returns distinct values because it compares each salary to the maximum.

Q: Can I retrieve all employees earning the second highest salary?
A:

A: Yes. To obtain every employee whose compensation matches the second‑highest distinct amount, you can either keep the rows whose rank equals 2 from the ranking CTE, or you can filter the base table with a subquery that returns that amount and join it back.

Using the ranking CTE

WITH ranked AS (
    SELECT *,
           DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
    WHERE salary IS NOT NULL
)
SELECT employee_id,
       name,
       department,
       salary
FROM ranked
WHERE rnk = 2;

Using a subquery

SELECT e.employee_id,
       e.name,
       e.department,
       e.salary
FROM employees e
JOIN (
    SELECT MAX(salary) AS second_max
    FROM employees
    WHERE salary < (SELECT MAX(salary) FROM employees)
      AND salary IS NOT NULL
) s ON e.salary = s.second_max
WHERE e.salary IS NOT NULL;

Both formulations return all rows that share the second‑highest compensation, automatically handling ties.

Additional considerations

Tie handling – Because the ranking functions assign the same rank to equal values, the queries above will include every employee that earns the same second‑highest amount. If you need a strict “exactly one row” result, you can add LIMIT 1 after the ORDER BY salary DESC in the subquery, but that sacrifices the tie‑aware nature of the result.

Performance – An index on salary (ideally covering the columns you select) lets the database locate the maximum value without scanning the whole table. In databases that support index‑only scans, the MAX()‑based subquery can be dramatically faster than a full sort, especially on very large tables Simple, but easy to overlook..

Materialized view – For scenarios where the second‑highest salary is queried often, creating a view that pre‑computes the amount can eliminate repeated calculations:

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

Now you can retrieve the full employee list with a simple join:

SELECT e.*
FROM employees e
JOIN vw_second_highest s ON e.salary = s.second_highest_salary;

NULL safety – Always filter out NULL salaries before performing comparisons, as shown in the examples. This prevents unexpected exclusion of rows and ensures the aggregate functions work on a deterministic set.

Conclusion

Obtaining the second‑highest salary is straightforward when only the amount is required: a MAX()‑based subquery efficiently yields the distinct value while automatically ignoring NULLs. To retrieve the associated employee records — including names, departments, or any other columns — use a ranking CTE (DENSE_RANK) or a join against the subquery result. Indexing the salary column and, when appropriate, materializing the result in a view can further enhance performance. By handling NULLs, accounting for ties, and choosing the right technique for your workload, you can reliably retrieve the second‑highest compensation and the employees who earn it It's one of those things that adds up. Turns out it matters..