Second Highest Salary In Sql Query

Introduction

Finding the second highest salary in SQL query is a classic interview question that tests a candidate’s grasp of subqueries, aggregate functions, and handling duplicates. Whether you are preparing for a technical interview, optimizing reporting logic, or building a salary‑benchmarking dashboard, mastering this pattern equips you with reusable techniques for any “N‑th highest” scenario. In this guide we will walk through the problem statement, explore multiple solutions, discuss their performance implications, and answer frequently asked questions so you can confidently apply the right approach in any relational database system.

Understanding the Problem

Before diving into code, clarify what “second highest salary” means in the context of a table named Employees with columns EmployeeID, Name, and Salary:

• If salaries are distinct, the second highest is simply the salary that ranks just below the maximum.
• When duplicate salaries exist, interpretations vary:
  • Distinct‑value interpretation – ignore duplicates and return the second unique salary.
  • Row‑based interpretation – treat each row separately; if the highest salary appears multiple times, the second highest could still be the same value.

Most interview expectations follow the distinct‑value interpretation unless explicitly stated otherwise. We will cover both perspectives.

Common Approaches to Retrieve the Second Highest Salary

1. Using ORDER BY with LIMIT / TOP

The simplest method sorts salaries descending and skips the first row.

-- MySQL, PostgreSQL, SQLite
SELECT Salary
FROM Employees
ORDER BY Salary DESC
LIMIT 1 OFFSET 1;

-- SQL Server
SELECT TOP 1 Salary
FROM (
    SELECT DISTINCT Salary
    FROM Employees
) AS DistinctSalaries
ORDER BY Salary DESC;

Pros: Easy to read, works well for small tables.
Cons: Sorting the entire dataset can be expensive on large tables; handling duplicates requires DISTINCT or grouping.

2. Using Subquery with MAX and <

A classic pattern finds the maximum salary that is less than the overall maximum.

SELECT MAX(Salary) AS SecondHighestSalary
FROM Employees
WHERE Salary < (SELECT MAX(Salary) FROM Employees);

Pros: No sorting; only two scans of the table.
Cons: Returns NULL if all salaries are identical or the table has fewer than two distinct values Still holds up..

3. Using DENSE_RANK() Window Function

Window functions assign ranks without collapsing rows, making it easy to filter by rank.

SELECT Salary
FROM (
    SELECT Salary,
           DENSE_RANK() OVER (ORDER BY Salary DESC) AS rnk
    FROM Employees
) AS ranked
WHERE rnk = 2;

Pros: Handles duplicates naturally; DENSE_RANK ensures the second distinct value gets rank 2 even if the top salary repeats.
Cons: Requires a database that supports window functions (most modern RDBMS do) Less friction, more output..

4. Using OFFSET … FETCH (ANSI SQL)

An ANSI‑standard alternative to LIMIT/OFFSET.

SELECT Salary
FROM Employees
ORDER BY Salary DESC
OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY;

Pros: Portable across ANSI‑compliant systems.
Cons: Same sorting cost as the LIMIT method.

5. Using GROUP BY and HAVING for Distinct Values

When you need to guarantee distinct salaries without relying on DISTINCT inside a subquery:

SELECT Salary
FROM Employees
GROUP BY Salary
ORDER BY Salary DESC
LIMIT 1 OFFSET 1;

Pros: Explicitly shows the intent to work with distinct groups.
Cons: Still involves a sort; performance depends on the optimizer’s ability to use indexes on Salary Not complicated — just consistent..

Step‑by‑Step Solution Walkthrough

Below is a detailed, database‑agnostic recipe you can adapt to MySQL, PostgreSQL, SQL Server, or Oracle.

Step 1: Examine the Data

SELECT DISTINCT Salary
FROM Employees
ORDER BY Salary DESC
LIMIT 5;

This quick peek reveals the spread of salaries and helps you decide whether duplicates are prevalent Small thing, real impact. But it adds up..

Step 2: Choose the Appropriate Technique

Step 3: Implement the Query

Example using DENSE_RANK (works in PostgreSQL, SQL Server, Oracle, MySQL 8.0+):

SELECT EmployeeID, Name, Salary
FROM (
    SELECT EmployeeID,
           Name,
           Salary,
           DENSE_RANK() OVER (ORDER BY Salary DESC) AS salary_rank
    FROM Employees
) AS ranked
WHERE salary_rank = 2;

If you only need the salary value:

SELECT MAX(Salary) AS SecondHighestSalary
FROM Employees
WHERE Salary < (SELECT MAX(Salary) FROM Employees);

Step 4: Validate Edge Cases

• Single distinct salary – both queries return NULL (or no rows). Decide whether to return NULL, a default value, or raise an error based on business rules.
• Empty table – similarly yields NULL. Guard against this in application code if needed.
• Negative salaries – the logic remains unchanged because ordering is based on numeric value.

Step 5: Optimize with Indexes

If the Salary column is not indexed, consider creating one:

CREATE INDEX idx_employees_salary ON Employees(Salary);

An index allows the optimizer to satisfy ORDER BY Salary DESC or MAX(Salary) without a full table scan, dramatically reducing execution time for large datasets The details matter here..

Scientific Explanation: Why These Queries Work

Sorting vs. Scanning

Sorting (ORDER BY) puts all rows in a total order, enabling direct access to the k‑th element via offset. Its complexity is O(n log n) in the worst case, though modern engines can use external merge sort or put to work existing indexes to approach O(n) when the index already provides the needed order And that's really what it comes down to. Turns out it matters..

Aggregation (MAX) scans the table once to find the highest value, then a second scan to find the maximum among values lower than that peak. This yields O(n) time with O(1) extra space, making it ideal when an index on Salary exists because

… the index can be used to retrieve the maximum via a simple index seek, and the second maximum via a range scan that stops after finding the first value less than the max, which reduces the I/O to roughly two leaf‑node traversals rather than a full table scan. So when the index is covering (i. e., includes the other columns needed in the SELECT list), the engine can satisfy the query entirely from the index, eliminating the need to touch the base table altogether.

Cost‑based considerations

Handling ties and duplicates

If the business rule treats duplicate salaries as a single rank (i.e., the second distinct salary), the DENSE_RANK() approach naturally collapses ties, whereas ROW_NUMBER() would assign arbitrary numbers to equal‑valued rows and could incorrectly return a row that is not truly the second distinct salary. Which means when duplicates should be preserved (e. g., you want every employee who earns the second highest amount), DENSE_RANK() is still appropriate because it returns all rows sharing that rank. If you need only the salary value and not the rows, the two‑pass MAX subquery automatically ignores duplicates because it looks for the greatest value that is strictly less than the maximum.

Parallelism and modern optimizers

Recent versions of major RDBMSs (PostgreSQL 13+, SQL Server 2019+, Oracle 21c, MySQL 8.Because of that, 0+) can parallelize both the index scan used for MAX and the sort required for window functions. When the table is partitioned on a column unrelated to Salary, each partition can be scanned independently, and the results merged with a minimal overhead. What this tells us is for terabyte‑scale employee tables, the two‑pass aggregate often outperforms a full sort even when the index is not perfectly covering, because the parallel scan cost grows linearly with the number of partitions while the sort cost grows with n log n and suffers from synchronization barriers.

Practical checklist

1. Verify index existence – SHOW INDEX FROM Employees or equivalent; create idx_employees_salary if missing.
2. Determine requirement – distinct second highest salary vs. list of employees earning that salary.
3. Choose method –
   • Small tables / no index → ORDER BY … LIMIT 1 OFFSET 1.
   • Large indexed table, only salary needed → two‑pass MAX.
   • Need employee details and tie handling → DENSE_RANK() window.
   • ANSI‑only environments → OFFSET … FETCH.
4. Test with EXPLAIN – confirm the optimizer uses an index seek/scan and not a costly sort.
5. Consider covering index – add INCLUDE (EmployeeID, Name) (or the equivalent syntax for your DBMS) to avoid look‑ups.
6. Handle edge cases – wrap the query in a `COALESCE(..., NULL