Sql Query To Select Second Highest Salary

SQL Query to Select Second Highest Salary

Finding the second highest salary is a classic interview question that tests a candidate’s ability to think beyond simple MAX() aggregates and to handle edge cases such as duplicate values or missing data. This guide walks through multiple reliable techniques, explains why each works, and highlights performance considerations so you can choose the best approach for your database system and data volume.

Introduction

When you need to retrieve the employee (or any record) with the second highest salary, a straightforward ORDER BY salary DESC LIMIT 1,1 might seem sufficient, but real‑world tables often contain ties, NULLs, or require portability across different SQL dialects. So naturally, understanding the underlying logic helps you write queries that are both correct and efficient. The sql query to select second highest salary can be expressed using subqueries, window functions, or vendor‑specific clauses, each with its own trade‑offs.

Understanding the Problem

Before diving into syntax, clarify the exact requirement:

1. Distinct salaries – If two employees earn the same highest salary, the second highest distinct salary should be returned.
2. Duplicate handling – Some interpretations ask for the employee record that sits in the second position when sorted descending, even if salaries repeat.
3. Existence check – Return NULL (or a designated placeholder) when fewer than two distinct salaries exist.

Most interview solutions focus on distinct salaries, which we will adopt as the default unless noted otherwise.

Common Approaches Overview

Below we detail each method, provide sample queries, and discuss when to prefer one over another.

1. Subquery Using MAX and <

The most portable solution works in virtually any SQL dialect:

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

How it works

1. The inner query (SELECT MAX(salary) FROM employees) returns the highest salary.
2. The outer query selects the maximum salary that is strictly less than that value, yielding the second highest distinct salary.
3. If only one distinct salary exists, the WHERE clause filters out all rows, and MAX(salary) returns NULL.

Advantages

• ANSI‑SQL compliant → runs on MySQL, PostgreSQL, SQL Server, Oracle, SQLite.
• Clear intent; easy to read for beginners.

Limitations

• Requires two full scans of the table (one for each MAX). With proper indexing on salary, each scan can be an index‑only lookup, keeping cost low.
• Does not return the employee row(s); only the salary value. To fetch rows, join back:

SELECT e.*
FROM employees e
WHERE e.salary = (
    SELECT MAX(salary)
    FROM employees
    WHERE salary < (SELECT MAX(salary) FROM employees)
);

2. Using ORDER BY … LIMIT … OFFSET

Many modern databases support the LIMIT/OFFSET clause (MySQL, PostgreSQL, SQLite, MariaDB). The query becomes concise:

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

Explanation

• DISTINCT salary removes duplicate salary values before ordering.
• ORDER BY salary DESC sorts from highest to lowest.
• OFFSET 1 skips the first (highest) distinct salary.
• LIMIT 1 returns the next row, i.e., the second highest distinct salary.

When to use

• Ideal for ad‑hoc reporting or when you only need the salary value.
• Performance is excellent if there is an index on salary because the database can stop after scanning enough rows to satisfy the LIMIT.

Fetching full rows

If you need the employee(s) with that salary (note there could be multiple employees sharing the second highest salary), join the result back:

SELECT e.*
FROM employees e
JOIN (
    SELECT DISTINCT salary
    FROM employees
    ORDER BY salary DESC
    LIMIT 1 OFFSET 1
) s ON e.salary = s.salary;

Caveats

• Not part of the SQL standard; will not run on Oracle (prior to 12c) or older SQL Server versions without rewriting.
• Without DISTINCT, the query returns the row that happens to be second in order, which may be a duplicate of the highest salary.

3. Window Functions: DENSE_RANK and ROW_NUMBER

Window functions provide a powerful, ANSI‑SQL way to rank rows without collapsing duplicates prematurely And that's really what it comes down to. Worth knowing..

3.1 Using DENSE_RANK (distinct salaries)

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

• DENSE_RANK assigns the same rank to equal salaries and does not skip numbers after ties.
• The outer query filters for rank = 2, giving the second highest distinct salary.

3.2 Using ROW_NUMBER (position‑based, includes duplicates)

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

• ROW_NUMBER gives a unique sequential number, so if the top salary appears multiple times, the second row may still be the same salary.
• Use this only when the requirement is “the employee that appears second after sorting,” regardless of salary duplication.

Fetching employee details

Both patterns can be extended to return full rows:

SELECT e.*
FROM employees e
JOIN (
    SELECT salary,
           DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
) r ON e.salary = r.salary
WHERE r.rnk = 2;

Performance notes

• Modern optimizers can compute the window function efficiently if there is an index on salary (often using an index‑only scan).
• The function requires a single pass over the data, making it scalable for large tables.

4. SQL Server‑Specific: OFFSET FETCH

Starting with SQL Server 2012, you can use the ANSI

Starting with SQL Server 2012, you can use the ANSI‑standard OFFSET … FETCH syntax, which behaves identically to the LIMIT/OFFSET approach but is portable across any database that implements the SQL:2008 standard (PostgreSQL, Oracle 12c+, MySQL 8.Plus, 0+, DB2, etc. ).

SELECT DISTINCT salary AS SecondHighestSalary
FROM employees
ORDER BY salary DESC
OFFSET 1 ROW FETCH NEXT 1 ROW ONLY;

• OFFSET 1 ROW skips the highest salary.
• FETCH NEXT 1 ROW ONLY returns exactly one distinct salary value.
• As with the LIMIT variant, wrap the query in a sub‑query or CTE and join back to employees if you need the full employee records.

Performance mirrors the LIMIT discussion: an index on salary allows the engine to stop after the required rows are read.

5. Correlated Subquery (Classic, Universal)

Before window functions and OFFSET/FETCH were widely available, the correlated subquery was the go‑to solution. It works on virtually every RDBMS, including legacy versions.

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

How it works
For each row in e1, the subquery counts how many distinct salaries are higher. The outer WHERE keeps only rows where that count equals 1—i.e., exactly one distinct salary is higher, which by definition is the second highest.

Fetching employee details

SELECT e.*
FROM employees e
WHERE e.salary = (
    SELECT MAX(salary)
    FROM employees
    WHERE salary < (SELECT MAX(salary) FROM employees)
);

Caveats

• Can be slower on large tables because the subquery executes conceptually once per row (though optimizers often transform it into a semi‑join).
• Requires DISTINCT inside the count to handle duplicates correctly.

6. Common Table Expressions (CTEs) for Readability

CTEs don’t introduce new logic but make complex queries easier to read and maintain. They are especially handy when you need to reuse the ranked result set multiple times Small thing, real impact..

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

You can also chain CTEs to first isolate distinct salaries, then rank them:

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

This pattern separates deduplication from ranking, which can help the optimizer and makes the intent crystal clear.

7. Handling Edge Cases

8. Quick Reference Cheat Sheet

rnk = 2)|SELECT * FROM employees WHERE salary = (SELECT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) rnk FROM employees) WHERE rnk = 2)| | **Legacy SQL Server (pre‑2012)** |SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees)|SELECT * FROM employees WHERE salary = (SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees))| | **Legacy Oracle (pre‑12c)** |SELECT * FROM (SELECT DISTINCT salary FROM employees ORDER BY salary DESC) WHERE ROWNUM = 2| Requires nested subquery orDENSE_RANK` workaround |

This is where a lot of people lose the thread And it works..

9. Performance Tuning Checklist

1. Index the sort column – A B‑tree index on salary (or a composite index like (salary, employee_id) if you select employee details) allows the engine to satisfy ORDER BY salary DESC without a full sort.
2. Avoid SELECT * in subqueries – Project only the columns you need (salary, employee_id) to keep intermediate result sets narrow.
3. Prefer DENSE_RANK over ROW_NUMBER for distinct logic – It eliminates the need for a separate DISTINCT pass in many execution plans.
4. Watch OFFSET on deep pages – While OFFSET 1 is trivial, OFFSET 100000 forces the engine to scan and discard rows. For "top‑N" queries, OFFSET 0 / FETCH FIRST N is optimal; for arbitrary pagination, consider keyset pagination (WHERE salary < last_seen_salary).
5. Analyze the plan – Look for Sort, Top‑N Sort, or Window Aggregate operators. A Top‑N Sort with an index seek is the ideal shape.

10. When to Use Which Method?

Real talk — this step gets skipped all the time Simple, but easy to overlook..

Conclusion

Finding the second highest salary is a deceptively simple requirement that exposes fundamental SQL concepts: duplicate handling, ordering guarantees, window semantics, and dialect-specific pagination syntax.

For modern applications, the DENSE_RANK CTE pattern strikes the best balance of readability, portability, and optimizer friendliness—it cleanly separates deduplication from ranking and works identically across PostgreSQL, SQL Server, Oracle, and MySQL 8+. When you only need the scalar value and run on a recent engine, the OFFSET 1 FETCH NEXT 1 clause is the most concise expression of intent.

Regardless of the syntax you choose, always pair the query with a supporting index on salary and verify the execution plan. A well-indexed "top‑N" query should execute in logarithmic time, turning a classic interview puzzle into a production-ready, millisecond-speed lookup.