Second Highest Salary Query In Sql

The second highest salary query in SQL is a common interview question that tests a candidate’s ability to manipulate data, understand window functions, and optimize performance. On top of that, In many relational databases, the task is to retrieve the employee record that earns the second greatest salary value without simply ordering the entire table and picking the second row, which can be inefficient on large datasets. This article explains the concept step by step, shows multiple reliable techniques, and addresses typical concerns such as ties, NULL values, and execution speed, making it a practical guide for developers, analysts, and students alike.

Introduction

Understanding how to fetch the second highest salary in SQL requires a clear grasp of set theory and ranking functions. The basic idea is to rank salaries from highest to lowest and then select the record whose rank equals 2. Even so, the implementation varies across database systems such as MySQL, PostgreSQL, SQL Server, and Oracle, each offering distinct syntax that can affect both readability and performance. By mastering these approaches, you can write solid queries that work consistently regardless of the underlying platform Practical, not theoretical..

Step‑by‑Step Methodology

1. Identify the Table and Column

Assume you have an employees table with the following structure:

• employee_id – primary key
• first_name
• last_name
• salary – numeric column representing annual compensation

The goal is to return the entire row (or at least the salary) of the employee who earns the second highest salary.

2. Choose a Ranking Approach

There are three primary strategies:

1. ROW_NUMBER() window function – assigns a unique sequential number to each row based on the ordering criteria.
2. RANK() or DENSE_RANK() window function – handles ties by assigning the same rank to equal salary values.
3. Self‑join or subquery with MAX() – traditional method that uses nested queries to isolate the second highest value.

Each method has its own advantages; the choice depends on whether you need to treat duplicate salaries as separate ranks or collapse them.

3. Write the Query

Below are the three approaches, each presented as a separate H3 section for clarity.

a) Using ROW_NUMBER()

SELECT employee_id, first_name, last_name, salary
FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
    FROM employees
) AS ranked
WHERE rn = 2;

• Explanation: The inner query orders all rows by salary descending and assigns a sequential number (rn). The outer query filters for rn = 2, which corresponds to the second highest salary.
• Pros: Simple, works even when salaries are unique.
• Cons: If multiple employees share the same top salary, ROW_NUMBER() will still assign distinct numbers, potentially skipping the true second highest value.

b) Using RANK() (or DENSE_RANK())

SELECT employee_id, first_name, last_name, salary
FROM (
    SELECT *, RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
) AS ranked
WHERE rnk = 2;

• Explanation: RANK() assigns the same rank to rows with identical salary values. If the highest salary appears twice, both rows receive rank 1, and the next distinct salary gets rank 2, correctly reflecting the second highest salary.
• Pros: Handles ties gracefully.
• Cons: Slightly more complex; may return multiple rows if there are ties for the second position.

c) Using a Self‑Join with MAX()

SELECT e.employee_id, e.first_name, e.last_name, e.salary
FROM employees e
JOIN (
    SELECT MAX(salary) AS second_max
    FROM employees
    WHERE salary < (SELECT MAX(salary) FROM employees)
) AS m ON e.salary = m.second_max;

• Explanation: The subquery first finds the maximum salary, then selects the maximum salary that is less than that value, effectively the second highest. The outer query matches the employee rows to this value.
• Pros: Works on databases that lack window functions (e.g., older MySQL versions).
• Cons: Requires two separate scans of the table, which can be slower on large datasets.

Scientific Explanation

How Ranking Functions Work

Window functions like ROW_NUMBER(), RANK(), and DENSE_RANK() operate on the result set before it is returned to the client. They compute a value for each row based on a specified order clause (e.g., ORDER BY salary DESC) The details matter here. Which is the point..

• ROW_NUMBER() guarantees a unique sequential number, even if the ordering column contains duplicates.
• RANK() gives the same rank to tied rows and leaves gaps in the sequence (e.g., 1, 1, 3).
• DENSE_RANK() also gives the same rank to ties but does not leave gaps (e.g., 1, 1, 2).

Understanding these behaviors is crucial when you need the second distinct salary versus the second row in a sorted list Worth keeping that in mind..

Performance Considerations

From a computational complexity perspective:

• ROW_NUMBER() and RANK() both require a sort operation, which is typically O(n log n). The overhead is comparable, but ROW_NUMBER() may be marginally faster because it does not need to handle tie logic.
• The self‑join method performs two aggregate scans (MAX twice) and a join, which can be more I/O intensive, especially if the table lacks appropriate indexes on salary.
• Indexing the salary column dramatically improves performance for all methods. To give you an idea, a non‑unique index on salary DESC allows the database to retrieve rows in the required order without an explicit sort.

Dealing with NULL Values

SQL treats NULL as the lowest possible value in ordering. If your salary column can contain NULLs, they will be placed at the bottom of a descending sort, potentially affecting the result. To exclude NULLs, add a WHERE salary IS NOT NULL clause inside the window function’s subquery or filter them in the outer query.

Example Queries and Variations

1. Return Only the Salary Value

If you need just the numeric salary rather than the whole row:

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

• This approach works in MySQL and PostgreSQL but is not portable to SQL Server (which uses FETCH NEXT).

2. Using Common Table Expressions (CTE)

A CTE improves readability, especially for complex ranking logic:

WITH ranked AS (
    SELECT *,
           RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
)
SELECT employee_id, first_name, last_name, salary
FROM ranked
WHERE rnk = 2;

3. Handling Multiple Second‑Highest Records

When ties exist, you may want all employees sharing the second highest salary:

SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary = (
    SELECT DISTINCT salary
    FROM employees
    ORDER BY salary DESC
    OFFSET 1 ROW FETCH NEXT 1 ROW ONLY
);

This query first isolates the second distinct salary and then returns every employee earning that amount And that's really what it comes down to..

Performance Tips

1. Create an Index:

   CREATE INDEX idx_employees_salary_desc ON employees (salary DESC);
   

   The index allows the optimizer to satisfy the ORDER BY clause directly from the index, avoiding a costly sort And it works..

2. **Avoid SELECT *** : Retrieve only the columns you need. Selecting large rows can increase memory usage and network traffic.

3. Limit Result Sets: If you only need the top few rows, add LIMIT (MySQL/PostgreSQL) or TOP (SQL Server) to reduce processing Worth keeping that in mind..

4. Analyze Execution Plan: Use EXPLAIN (or its vendor‑specific variant) to verify that the query uses the index and that no unnecessary full table scans occur.

Frequently Asked Questions (FAQ)

Q1: What if there are multiple employees with the same highest salary?
A: Using RANK() will assign rank 1 to all of them, and the next distinct salary will receive rank 2, correctly returning the second highest salary. ROW_NUMBER() would still give distinct numbers, potentially skipping the true second salary Not complicated — just consistent..

Q2: Can I use this technique in Oracle?
A: Yes. Oracle supports ROW_NUMBER(), RANK(), and DENSE_RANK() in exactly the same way. The syntax is identical to the examples provided.

Q3: Does the query work on large tables with millions of rows?
A: With an appropriate index on salary, the window functions are efficient enough for millions of rows. The self‑join method may become a bottleneck because it requires two full scans unless indexed Turns out it matters..

Q4: Is there a built‑in function to get the nth highest value?
A: No standard SQL function exists for “nth highest.” The common practice is to use window functions or subqueries as demonstrated Simple, but easy to overlook. Surprisingly effective..

Q5: How do I handle decimal salaries versus integer salaries?
A: The data type does not affect the ranking logic; however, see to it that the column is numeric (e.g., DECIMAL(10,2)) to preserve fractional values accurately.

Conclusion

Retrieving the second highest salary in SQL is a deceptively simple problem that reveals deeper concepts about ranking, performance tuning, and database-specific syntax. Remember to index the salary column, filter out NULLs when necessary, and always test your query with realistic data to confirm that it returns the expected results. Think about it: by mastering the three primary techniques—ROW_NUMBER(), RANK(), and self‑join with MAX()—you can select the method that best fits your data volume, tie‑handling requirements, and database platform. With these skills, you’ll be well‑equipped to answer interview questions confidently and write production‑ready SQL code that scales efficiently.