Sql Query To Retrieve Second Highest Salary

SQL Query to Retrieve Second Highest Salary: A Complete Guide for Developers

Finding the second highest salary in a database is one of the most common SQL interview questions and practical scenarios faced by developers working with employee management systems. Whether you're preparing for a technical interview, optimizing a payroll query, or simply expanding your SQL knowledge, mastering this concept is essential for any database professional. This practical guide explores multiple approaches to retrieve the second highest salary using standard SQL techniques, window functions, and subqueries That's the whole idea..

Understanding the Problem

Before diving into solutions, it's crucial to understand what we're trying to achieve. Consider an employees table with the following structure:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10,2)
);

Our goal is to write a query that returns the second highest distinct salary value from this table. it helps to note that if multiple employees share the same salary, we want the second highest distinct salary amount, not the second row in a sorted list.

Method 1: Using Subquery with LIMIT and OFFSET

One of the simplest approaches involves using a subquery with LIMIT and OFFSET clauses:

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

How it works:

ORDER BY salary DESC sorts all salaries in descending order
DISTINCT ensures duplicate salary values are removed
LIMIT 1 OFFSET 1 skips the first result (highest salary) and returns only the second row

This method is straightforward and readable, making it ideal for beginners. That said, it's worth noting that OFFSET can have performance implications on large datasets since the database engine must still process all rows before the offset point.

Method 2: Using Subquery with MAX Function

Another classic approach uses nested subqueries with the MAX function:

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

How it works:

The inner subquery (SELECT MAX(salary) FROM employees) finds the highest salary
The outer query selects the maximum salary that is less than the highest salary
This effectively returns the second highest salary

This approach is portable across different SQL dialects and performs well because it leverages index-based lookups rather than sorting entire result sets But it adds up..

Method 3: Using Window Functions (Modern Approach)

For databases supporting window functions (SQL Server, PostgreSQL, Oracle, MySQL 8.0+), the DENSE_RANK() function provides an elegant solution:

SELECT DISTINCT salary
FROM (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) as rank
    FROM employees
) ranked_salaries
WHERE rank = 2;

How it works:

DENSE_RANK() OVER (ORDER BY salary DESC) assigns ranks to salaries without gaps
The subquery creates a derived table with salary values and their corresponding ranks
The outer query filters for records where rank equals 2
DISTINCT ensures unique salary values in the final result

Window functions are particularly powerful because they can easily be extended to find the Nth highest salary by simply changing the rank condition The details matter here. Turns out it matters..

Method 4: Using Common Table Expression (CTE)

Common Table Expressions offer improved readability and can make complex queries more maintainable:

WITH RankedSalaries AS (
    SELECT DISTINCT salary,
           ROW_NUMBER() OVER (ORDER BY salary DESC) as row_num
    FROM employees
)
SELECT salary AS second_highest_salary
FROM RankedSalaries
WHERE row_num = 2;

How it works:

The CTE RankedSalaries creates a temporary result set with distinct salaries and their row numbers
ROW_NUMBER() assigns sequential integers to rows ordered by salary descending
The main query selects the salary where row number equals 2

Note that ROW_NUMBER() treats ties differently than DENSE_RANK() – if two employees have the same highest salary, ROW_NUMBER() would assign them different row numbers, potentially affecting results.

Handling Edge Cases

When implementing these solutions, consider several edge cases that could cause unexpected behavior:

Empty Tables: If the employees table contains no records, all queries will return empty result sets, which is the correct behavior.

Single Record Tables: When only one employee exists, there is no second highest salary. Queries using MAX comparisons will return NULL, while LIMIT/OFFSET approaches will return empty results.

Duplicate Highest Salaries: If multiple employees share the highest salary, methods using DENSE_RANK() or MAX comparisons will correctly identify the second highest distinct salary, while ROW_NUMBER() might behave unexpectedly depending on implementation.

Performance Considerations

The choice of method should consider database size and indexing:

Indexed Columns: Queries using comparison operators (WHERE salary < ...) benefit significantly from indexes on the salary column
Small Datasets: For tables with fewer than a few thousand records, performance differences are negligible
Large Datasets: Window functions and indexed subqueries generally outperform LIMIT/OFFSET approaches
Memory Usage: Subqueries that sort entire result sets consume more memory than targeted comparisons

Extending to Nth Highest Salary

Most of these methods can be easily adapted to find the Nth highest salary:

-- Using LIMIT/OFFSET for Nth highest
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET N-1;

-- Using DENSE_RANK for Nth highest
SELECT DISTINCT salary
FROM (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) as rank
    FROM employees
) ranked_salaries
WHERE rank = N;

Practical Applications Beyond Interviews

While commonly featured in coding interviews, retrieving specific ranked values has real-world applications:

Payroll Analysis: Identifying compensation benchmarks within organizations
Performance Reviews: Setting salary bands based on percentile rankings
Budget Planning: Understanding salary distribution for workforce planning
Competitive Analysis: Comparing compensation packages against market data

Conclusion

Retrieving the second highest salary demonstrates fundamental SQL concepts including subqueries, sorting, ranking, and set operations. So naturally, each method has advantages depending on your database system, data size, and specific requirements. On the flip side, the subquery with MAX function offers maximum compatibility across SQL dialects, while window functions provide flexibility for more complex ranking scenarios. Understanding these approaches not only helps in technical interviews but also builds foundational knowledge for writing efficient, maintainable SQL queries in production environments.

By practicing these techniques and understanding their underlying mechanics, developers can confidently tackle similar ranking problems and write more sophisticated database queries that scale effectively with growing data volumes.

Handling Edge Cases

When the data contains NULL values in the salary column, most SQL dialects treat NULL as the lowest possible value in ordering, which can skew the result. To exclude NULLs explicitly, add a WHERE salary IS NOT NULL clause inside the subquery or the main query, depending on the desired semantics Worth keeping that in mind..

If the table holds only a single distinct salary, any of the techniques will return that value (or an empty set if you require a truly “second” value). In such cases it is prudent to guard the query with a HAVING COUNT(DISTINCT salary) >= 2 check, or to anticipate a fallback result in application code Turns out it matters..

Duplicate rows do not affect the logical outcome when DISTINCT is used, but they can inflate the cost of a ROW_NUMBER() window if the underlying table is extremely wide. Here's the thing — filtering out duplicates early — e. g., SELECT DISTINCT employee_id, salary FROM employees — can reduce the volume that must be sorted Nothing fancy..

The official docs gloss over this. That's a mistake.

Advanced Ranking Patterns

Beyond the basic MAX subquery and DENSE_RANK() approaches, several dialects offer concise syntax for top‑N retrieval:

ANSI‑SQL FETCH FIRST – Supported by PostgreSQL 13+, Oracle 12c+, DB2, and SQL Server 2012+:

SELECT salary
FROM employees
ORDER BY salary DESC
FETCH FIRST 1 ROWS ONLY;   -- first highest
FETCH FIRST 1 ROWS ONLY OFFSET 1 ROW; -- second highest

QUALIFY (Snowflake, BigQuery) – Allows filtering on window function results without a subquery:
```
SELECT DISTINCT salary
FROM employees
QUALIFY DENSE_RANK() OVER (ORDER BY salary DESC) = 2;
```

CROSS APPLY (SQL Server) – Useful when the ranking logic is encapsulated in a table‑valued function:

SELECT TOP 1 salary
FROM employees e
CROSS APPLY (
    SELECT MAX(salary) AS max_salary
    FROM employees
    WHERE salary < e.salary
) AS p
WHERE p.max_salary IS NOT NULL;

These patterns can reduce the need for explicit subqueries and make the intent clearer, especially for developers accustomed to a particular dialect It's one of those things that adds up..

Performance Tuning Tips

Covering Indexes – An index that includes the salary column (and any additional filters) can satisfy the query without touching the base table. For the MAX method, a simple non‑clustered index on salary is usually sufficient. When using window functions, a descending index on salary can allow the optimizer to read the rows in the required order directly, avoiding an explicit sort step.
Execution Plan Inspection – In PostgreSQL, MySQL, or SQL Server, examine the plan to verify whether a Sort or Index Seek operation is used. If a sort appears, consider adding the appropriate index or rewriting the query to put to work the index more aggressively.
Materialized Intermediate Results – For very large tables, calculate the distinct salary list once and store it in a temporary table or a materialized view. Subsequent ranking queries then operate on a much smaller, pre‑aggregated set, dramatically cutting runtime And that's really what it comes down to. Which is the point..
Batch Processing – When the same ranking logic is required repeatedly (e.g., daily payroll reports), pre‑compute the second‑highest salary during an ETL step and store it alongside the employee record. This eliminates the need for runtime calculation entirely.

Real‑World Example: Department‑Level Benchmarks

Suppose a multinational wants to compare each department’s second‑highest compensation. The query can be extended using a window partition:

SELECT department_id,
       DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM employees
QUALIFY salary_rank = 2;

Here, QUALIFY (or a subquery) limits the output to the rank‑2 salary per department, enabling HR analysts to spot outliers, set department‑specific salary bands, and align compensation strategies with market benchmarks.

Final Takeaway

Retrieving the second highest salary is more than a textbook exercise; it illustrates how SQL’s set‑based nature, ordering capabilities, and window functions can be combined to solve practical business problems efficiently. While the MAX‑based subquery remains the most portable solution, modern databases provide richer tools — window functions, FETCH FIRST, QUALIFY, and indexing tricks — that can improve both readability and performance. By understanding the trade‑offs and applying the appropriate technique for the context, developers can write dependable, scalable queries that stand up to growing data volumes and evolving reporting needs But it adds up..

Sql Query To Retrieve Second Highest Salary