Get Second Highest Salary In Sql

How to Get the Second Highest Salary in SQL

To retrieve the second highest salary in sql, developers can employ window functions, subqueries, or ordering techniques, each offering distinct performance and readability advantages. Understanding these methods enables you to choose the most efficient approach for your specific database system and data volume That's the part that actually makes a difference..

Introduction

The second highest salary in sql query is a common interview question and a practical task for analysts who need to segment compensation data. Whether you are working with MySQL, PostgreSQL, SQL Server, Oracle, or SQLite, the underlying concepts remain the same: you must isolate the top two values and then extract the second one. This article walks you through several reliable techniques, explains the theory behind them, and answers frequently asked questions to ensure you can implement the solution confidently.

Step‑by‑Step Methods

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

1. Order the salary column in descending order so the highest value appears first.
2. Skip the first row (the highest salary) and return the next row only.

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

• Why it works: ORDER BY salary DESC sorts all salaries from highest to lowest. LIMIT 1 restricts the result set to a single row, while OFFSET 1 skips the first row, effectively selecting the second highest salary Still holds up..

• Pros: Simple, readable, and works on most relational databases.

• Cons: If the table is large, the database must sort the entire column, which can be costly for very big datasets Simple as that..

2. Using DENSE_RANK() Window Function (SQL Server, PostgreSQL, Oracle, MySQL 8.0+)

Window functions calculate a ranking for each row without collapsing the result set It's one of those things that adds up..

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

• Explanation: DENSE_RANK() assigns a unique rank to each distinct salary value, starting at 1 for the highest. By filtering WHERE rnk = 2, you retrieve the second highest distinct salary And that's really what it comes down to..

• Advantages: Handles ties gracefully; if multiple employees share the highest salary, the second rank still points to the next distinct salary value Small thing, real impact..

• Limitations: Slightly more complex syntax and may require additional processing compared to a simple LIMIT query And it works..

3. Using ROW_NUMBER() Window Function

If you need the second row regardless of duplicate salary values, ROW_NUMBER() is useful.

SELECT salary
FROM (
    SELECT salary,
           ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
    FROM employees
) AS numbered
WHERE rn = 2;

• Key point: ROW_NUMBER() numbers rows sequentially, so even if the highest salary appears multiple times, the second row will be the second occurrence, not necessarily the next distinct salary Simple as that..

• When to use: Situations where you want the exact second row in the ordered list, not the second distinct value.

4. Using a Correlated Subquery

A classic approach that works across virtually all SQL dialects.

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

• How it works: The inner query finds the maximum salary (the highest). The outer query then looks for the maximum salary that is less than that value, effectively the second highest Turns out it matters..

• Benefits: No window functions required, making it compatible with older database versions.

• Drawbacks: May perform poorly on large tables because the subquery scans the table twice.

5. Using Common Table Expressions (CTE) for Readability

Combining CTEs with ROW_NUMBER() or DENSE_RANK() improves clarity.

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

• Why CTEs help: They separate the ranking logic from the final filter, making the query easier to read and maintain.

Scientific Explanation

Understanding the second highest salary in sql query involves grasping two core concepts: ordering and ranking.

1. Ordering arranges rows based on a specified criterion—here, the salary column sorted in descending order. This operation is fundamentally a total order on the set of salary values.

2. Ranking functions (DENSE_RANK, ROW_NUMBER) assign a sequential identifier to each row after ordering. They are windowed because they compute the rank without collapsing the result set, preserving the original row structure.

From a set‑theoretic perspective, the problem reduces to finding the element with the second smallest ordinal in a totally ordered set. The LIMIT/OFFSET method directly extracts the element at position 2 after sorting, while window functions compute the ordinal implicitly and then filter for the desired rank. Correlated subqueries achieve the same result by iteratively applying the max aggregation under a conditional constraint (salary < max(salary)).

Performance considerations hinge on indexing. An index on the salary column (ideally descending) allows the database to avoid a full table sort, dramatically speeding up ORDER BY and window function evaluations. Without such an index, the cost grows linearly with table size Simple as that..

Most guides skip this. Don't Most people skip this — try not to..

FAQ

Q1: What if there are duplicate highest salaries?
Using DENSE_RANK() will treat duplicate highest salaries as rank 1, so the second rank still points to the next distinct salary. ROW_NUMBER() will count each row separately, meaning the second row could still be another instance of the highest salary. Choose the function that matches the business definition of “second highest”.

Q2: Can I get the second highest salary per department?
Yes. Add a partitioning clause to the window function:

DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk

Then filter WHERE rnk = 2 to obtain the second highest salary within each department Simple as that..

Q3: Does the LIMIT/OFFSET approach work in Oracle?
Oracle uses FETCH FIRST syntax:

SELECT salary
FROM employees
ORDER BY salary DESC
FETCH FIRST 1 ROW ONLY OFFSET 1 ROW ONLY;

Alternatively, use a subquery with ROW_NUMBER() as shown earlier.

Q4: Is there a way to retrieve the second highest salary without using subqueries or window functions?
Yes, the simple ORDER BY … LIMIT 1 OFFSET 1 pattern (or its Oracle equivalent) is the most straightforward and requires no extra constructs Surprisingly effective..

Q5: How does indexing affect performance?
An index on salary (preferably a descending index) lets the optimizer retrieve the top values directly from the index leaf pages, avoiding a costly sort operation. This is especially beneficial when the table contains millions of rows.

Conclusion

Retrieving the second highest salary in sql is a versatile problem that can be solved with several techniques, each suited to different database systems and performance requirements. Also, the ORDER BY … LIMIT/OFFSET method offers simplicity and broad compatibility, while window functions like DENSE_RANK() provide reliable handling of ties and clearer semantics. Correlated subqueries remain a reliable fallback for older environments. By understanding the underlying ordering and ranking concepts, and by applying appropriate indexing, you can efficiently obtain the second highest salary across any SQL‑based application. Use the approach that best matches your data volume, DBMS capabilities, and readability preferences, and you’ll consistently deliver accurate results It's one of those things that adds up..