Select Second Highest Salary In Sql

Selecting the second highest salary in SQL is a common interview question and a practical requirement when you need to analyze compensation data, identify outliers, or build reports that focus on top earners beyond the maximum value. This article explains multiple reliable techniques, discusses how to handle duplicate salaries, evaluates performance implications, and provides clear examples you can adapt to your own database schema.

Understanding the Problem

Before diving into solutions, it is essential to clarify what “second highest salary” means. If the highest salary appears more than once, the second highest is the next distinct value lower than the maximum. Here's one way to look at it: given salaries [10000, 10000, 8000, 6000], the highest salary is 10000 and the second highest distinct salary is 8000. Some variations ask for the second highest row (including duplicates), but the most common interpretation in SQL interviews is the second distinct salary.

Common Approaches

Several SQL constructs can retrieve the second highest salary. Each method has its own advantages regarding readability, portability, and efficiency.

Using ORDER BY and LIMIT/OFFSET

The simplest and most portable technique works in MySQL, PostgreSQL, SQLite, and many other dialects:

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

Explanation

1. DISTINCT salary removes duplicate salary values so that the ordering reflects unique compensation levels.
2. ORDER BY salary DESC sorts the distinct salaries from highest to lowest.
3. LIMIT 1 OFFSET 1 skips the first row (the highest salary) and returns the next row, which is the second highest distinct salary.

If your database does not support LIMIT/OFFSET (e.g., older versions of Oracle), you can achieve the same result with FETCH FIRST or ROW_NUMBER Practical, not theoretical..

Using a Subquery with MAX

A classic approach uses a subquery to exclude the maximum salary and then finds the maximum of the remaining set:

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

Explanation

• The inner query (SELECT MAX(salary) FROM employees) obtains the highest salary.
• The outer query selects the maximum salary that is strictly less than that value, effectively giving the second highest distinct salary.
• This method works across virtually all SQL platforms, including Oracle, SQL Server, MySQL, and PostgreSQL.

Using DENSE_RANK Window Function

Window functions provide a powerful way to rank rows without collapsing them via GROUP BY. DENSE_RANK assigns consecutive ranks, leaving no gaps when duplicate values exist:

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

Explanation

• The inner query computes a rank for each salary, ordering from highest to lowest.
• DENSE_RANK ensures that identical salaries receive the same rank, and the next distinct salary gets the next integer rank.
• The outer filter WHERE rnk = 2 picks the row(s) with rank 2, which correspond to the second highest distinct salary.
• If you need only one value, you can wrap the query with SELECT DISTINCT or add FETCH FIRST 1 ROW ONLY.

Using Correlated Subquery

A correlated subquery counts how many distinct salaries are greater than the current row’s salary. When that count equals 1, the row is the second highest:

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

Explanation

• For each salary e1.salary, the subquery counts how many distinct salaries are strictly greater.
• If exactly one distinct salary is greater, then e1.salary is the second highest.
• This method is intuitive but can be slower on large tables because the subquery executes for each row.

Using RANK vs DENSE_RANK

One thing to note the difference between RANK and DENSE_RANK. In real terms, RANK leaves gaps when there are ties (e. If you mistakenly use RANK and look for rank 2, you would skip the second highest distinct salary when the highest salary has duplicates. Consider this: , salaries 10000, 10000, 8000 produce ranks 1, 1, 3). Consider this: g. Which means, DENSE_RANK is the safer choice for this problem Which is the point..

Handling Duplicates

Duplicate salaries are the main source of confusion. The techniques above already address duplicates in different ways:

• DISTINCT + LIMIT/OFFSET removes duplicates before ordering, guaranteeing that the offset skips only distinct values.
• MAX with < (SELECT MAX…) inherently ignores duplicates because the comparison is based on value, not row count.
• DENSE_RANK treats equal salaries as the same rank, so the second rank always points to the next lower distinct value.
• Correlated subquery uses COUNT(DISTINCT …) to ensure duplicates do not inflate the greater‑than count.

If the business requirement is to return the second highest row (including duplicates), you would omit DISTINCT and use OFFSET 1 without worrying about distinct values, or you would look for RANK = 2 instead of DENSE_RANK = 2. Always clarify the exact interpretation before implementing And that's really what it comes down to..

Performance Considerations

When dealing with large employee tables, the choice of method can affect query speed:

the query optimizer can short-circuit after finding the first matching value in each scan. This approach is often the fastest because it avoids sorting or windowing entirely.

• Window functions (DENSE_RANK) | Moderate to good | Can make use of an index on salary for the window partition, though the window operation itself may require sorting | Efficient for complex ranking scenarios; however, for simply fetching the second highest value, the overhead of computing ranks for all rows can be unnecessary.
• Correlated subquery | Poor to moderate | May not benefit from indexes effectively due to the row-by-row evaluation | Each row triggers a new subquery execution, leading to O(n²) complexity in the worst case. Avoid this method on large datasets unless absolutely necessary.

Index Recommendations

To optimize any of these approaches, confirm that the salary column is indexed. A simple index on salary allows the database to quickly locate maximum values, traverse distinct entries in sorted order, or efficiently compute window functions. For example:

CREATE INDEX idx_employees_salary ON employees(salary);

This index supports all methods discussed, particularly those involving MAX, ORDER BY, or range comparisons like salary < (...).

Conclusion

Retrieving the second highest salary in SQL requires careful consideration of duplicate handling and performance trade-offs. The MAX with subquery approach offers the best balance of simplicity and efficiency for most use cases, especially when supported by an appropriate index. Window functions like DENSE_RANK provide flexibility for more complex ranking requirements but come with additional overhead. That said, correlated subqueries, while conceptually straightforward, should generally be avoided on large datasets due to their poor scalability. By understanding the nuances of each method and aligning them with business requirements—whether distinct values or raw rows matter—you can confidently choose the optimal solution for your specific scenario.