Get The Second Highest Salary In Sql

Finding the Second Highest Salary in SQL: A complete walkthrough

Finding the second highest salary in SQL is a common requirement for HR analysts, data engineers, and anyone who works with employee databases. Whether you need to identify the second‑best paid employee, compare compensation tiers, or simply explore ranking techniques, SQL provides several dependable methods to retrieve this information. This article walks you through the most reliable approaches, explains the underlying logic, and includes practical examples you can copy‑paste into your own queries.

Why You Might Need the Second Highest Salary

• Compensation analysis: Determine how the second‑highest earner compares to the top salary.
• Performance reviews: Benchmark the pay of high‑performing staff against the market.
• Reporting: Generate reports that highlight pay bands without exposing the absolute top earners.

Understanding these use cases helps you choose the right SQL query second highest salary technique for your environment.

Core Concepts Behind Ranking Salaries

Before diving into specific queries, it’s helpful to review the ranking functions SQL offers:

These window functions are usually paired with ORDER BY salary DESC to place the highest earners first.

Method 1: Using DENSE_RANK() for Simple Scenarios

DENSE_RANK() is ideal when you want to retrieve the second highest salary and you don’t mind handling ties gracefully.

SELECT employee_id, name, salary
FROM employees
WHERE DENSE_RANK() OVER (ORDER BY salary DESC) = 2;

How it works

1. The window function DENSE_RANK() OVER (ORDER BY salary DESC) creates a ranking column where the highest salary gets rank 1, the next distinct salary gets rank 2, and so on.
2. The outer WHERE clause filters for rows where the rank equals 2, returning all employees whose salary is the second highest, even if multiple people share that amount.

When to choose this method

• You need a straightforward answer and ties are acceptable.
• You’re using databases that support window functions (most modern RDBMS do).

Method 2: Using ROW_NUMBER() for Unique Results

If you must return only one employee (or you want to break ties arbitrarily), ROW_NUMBER() guarantees a single row.

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

Explanation

• The inner query assigns a sequential number (rn) to each row based on descending salary.
• The outer query filters for rn = 2, giving you exactly one employee—the one with the second highest salary after ordering.

Use case

• You are building a leaderboard where each position must be unique.
• You plan to paginate results based on rank.

Method 3: Using RANK() When You Need to Skip Numbers

RANK() behaves similarly to DENSE_RANK() but leaves gaps in the ranking sequence when ties occur. This can be useful for reporting that reflects “position” rather than “distinct value” Nothing fancy..

SELECT employee_id, name, salary
FROM employees
WHERE RANK() OVER (ORDER BY salary DESC) = 2;

Example scenario

If three employees earn the top salary, they all receive rank 1, and the next distinct salary receives rank 4. Using RANK() will skip ranks 2 and 3, which might be what you want for a “position‑based” report Not complicated — just consistent..

Method 4: A One‑Liner with LIMIT and OFFSET (MySQL / PostgreSQL)

Some databases allow a more concise approach using LIMIT and OFFSET. This technique works well when you only need the second row after ordering Less friction, more output..

SELECT employee_id, name, salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

Notes

• LIMIT 1 OFFSET 1 tells the database to skip the first row (the highest salary) and return the next row.
• This method does not handle ties; if multiple employees share the top salary, the second row could still be the same salary.
• Supported by MySQL, PostgreSQL, SQLite, and others.

Handling Edge Cases

1. Fewer Than Two Employees

If your table contains only one employee, all the above queries will return an empty result set. To avoid confusion, you can add a check:

SELECT employee_id, name, salary
FROM employees
WHERE DENSE_RANK() OVER (ORDER BY salary DESC) = 2
UNION ALL
SELECT NULL AS employee_id, 'No second highest salary' AS name, NULL AS salary
WHERE NOT EXISTS (
    SELECT 1 FROM employees WHERE DENSE_RANK() OVER (ORDER BY salary DESC) = 2
);

2. Multiple Employees Sharing the Second Highest Salary

When several staff members earn the same salary that is the second highest, DENSE_RANK() and RANK() will return all of them. Plus, g. This is often the desired behavior, but if you need to limit the output, you can combine the ranking with ROW_NUMBER() and a tie‑breaker column (e., employee_id).

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

3. Different SQL Dialects

• SQL Server: Uses TOP or OFFSET FETCH. Example with OFFSET:

  SELECT employee_id, name, salary
  FROM employees
  ORDER BY salary DESC
  OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY;
  

• Oracle: Supports ROWNUM or analytic functions. Example:

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

Step‑by‑Step Walkthrough

Below is a practical walkthrough you can follow in any SQL environment:

1. Create a sample employee table (if you don’t already have one):

   CREATE TABLE employees (
       employee_id INT PRIMARY KEY,
       name VARCHAR(100),
       salary DECIMAL(10,2)
   );
   
   INSERT INTO employees VALUES
   (1, 'Alice', 120000),
   (2, 'Bob', 110000),
   (3, 'Carol', 110000),
   (4, 'David', 95000),
   (5, 'Eve', 80000);
   

2. Run the DENSE_RANK() query to see the second highest salary (including ties):

   SELECT employee_id, name, salary
   FROM employees
   WHERE DENSE_RANK()
   
   

SELECT employee_id, name, salary
FROM employees
WHERE DENSE_RANK() OVER (ORDER BY salary DESC) = 2;

What This Query Does

• DENSE_RANK() assigns a rank to each row based on the salary column, ordered from highest to lowest.
• Because DENSE_RANK() does not skip numbers when ties occur, the top‑paid employee(s) receive rank 1, the next distinct salary receives rank 2, and so on.
• The WHERE clause filters the result set to only those rows whose rank equals 2, which gives you the second‑highest distinct salary and every employee earning it.

Expected Output for the Sample Data

Running the query against the sample table created in the walkthrough yields:

Both Bob and Carol share the second‑highest salary because Alice’s $120,000 is the highest, and the next distinct salary level is $110,000 That's the part that actually makes a difference..

When to Prefer DENSE_RANK() Over LIMIT 1 OFFSET 1

If your goal is strictly to “skip the highest earner and return the next row,” LIMIT 1 OFFSET 1 is concise. Even so, when you must correctly handle duplicate top salaries, need the salary itself in the output, or prefer a self‑documenting approach, DENSE_RANK() = 2 is the more dependable choice.

Short version: it depends. Long version — keep reading.

A Quick One‑Liner Alternative (MySQL 8.0+, PostgreSQL, SQLite)

For environments that support window functions, you can also achieve the same result with a subquery:

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

This pattern is useful when you need to apply additional filters or joins after ranking.

Final Thoughts

Finding the second highest salary is a common interview question, but its real‑world utility appears whenever you need to “skip the top performer” while respecting salary ties. The article has covered:

1. The basic LIMIT 1 OFFSET 1 trick and its pitfalls.
2. Edge cases such as tables with fewer than two rows and multiple employees sharing the second highest pay.
3. Dialect‑specific implementations for SQL Server, Oracle, and other major RDBMS.
4. A step‑by‑step walkthrough that you can run in any SQL environment.

When you choose a solution, consider three factors:

1. Correctness – Do you need to handle ties?
2. Readability – Will future maintainers understand the intent without comments?
3. Portability – Does the chosen syntax work across all the databases you target?

By weighing these criteria, you can confidently pick the approach that best fits your data set and development standards. Worth adding: whether you settle on LIMIT 1 OFFSET 1, DENSE_RANK() = 2, or a dialect‑specific trick, you now have a complete toolkit to retrieve the second highest salary accurately and efficiently. Happy querying!

Beyond the straightforward use case, it’s worth exploring how each technique behaves under different data shapes and performance constraints.

How LIMIT 1 OFFSET 1 Fails with Ties

When the table contains several employees who share the highest salary—say Alice, Bob, and Carol all earn $120,000—the LIMIT 1 OFFSET 1 query will discard the first row only. If you rely on this result to represent “the second‑highest distinct salary,” you may mistakenly return an employee whose salary equals the maximum, rather than the true runner‑up. Conversely, if you expect exactly one row back, the OFFSET skips over every other qualifying record, which can be both confusing and error‑prone The details matter here..

Why DENSE_RANK() Handles Duplicates Gracefully

The window function assigns a rank based purely on position in the ordered set, regardless of whether values are equal. In the example above, Alice, Bob, and Carol would all receive rnk = 1. The next distinct salary level would get rnk = 2, even if there are many ties at the higher level. This makes DENSE_RANK() ideal when the business rule explicitly states “return everyone who shares the second‑highest salary.

Most guides skip this. Don't.

Preserving Salary Information

A common oversight is assuming that retrieving just the row IDs gives you access to the actual amounts. With LIMIT 1 OFFSET 1, you lose the salary column unless you manually join back to the source table. So by wrapping the ranking in a derived table (as shown earlier), the outer SELECT can still pull salary alongside employee_id and name. This preserves the needed information without sacrificing readability Small thing, real impact..

Performance Considerations

• Index Usage: Both approaches benefit from an index on (salary) (or a composite index that includes salary). For LIMIT 1 OFFSET 1, the optimizer can quickly skip to the first row after an index scan. For DENSE_RANK(), the function must evaluate the entire sorted sequence, but a single index on salary allows efficient ordering as well.
• Sorting Cost: DENSE_RANK() requires sorting the whole result set before assigning ranks. On very large tables where the number of rows far exceeds the number of distinct salaries, this cost becomes non‑trivial. On the flip side, because the sort is typically performed once per query, modern MPP engines often mitigate latency through parallel execution.
• Memory Footprint: Window functions materialize the full ranked set in memory (or spill to disk) before filtering. If the dataset is massive, consider pagination (OFFSET … LIMIT) combined with a separate subquery that applies the rank, allowing you to stop early once enough rows have been processed.

Dialect‑Specific Nuances

While DENSE_RANK() is standard SQL, some platforms expose additional features that simplify the pattern:

• PostgreSQL & Oracle: You can combine DENSE_RANK() with FETCH FIRST n ROWS ONLY for explicit control over row counts.
• SQL Server: The TOP 1 WITH TIES clause mimics the behavior of LIMIT 1 OFFSET 1 but returns all rows that tie for the top position, making it another candidate when you want to treat ties uniformly.
• MySQL < 8.0: The older version lacked window functions, so developers relied solely on LIMIT. Starting from MySQL 8.0, the windowed solution presented earlier works unchanged.

Testing Your Implementation

Before deploying the solution, run sanity checks:

1. Edge Cases – Verify behavior with:
   • Only one employee in the table → should either raise an error or return an empty set.
   • All salaries identical → ensure you receive the correct count (e.g., DENSE_RANK() yields rank 1 for every row).
   • Nulls in salary – decide whether they should be excluded or treated as the lowest possible value.
2. Performance Benchmarks – Execute the queries against realistic data volumes and compare response times. Pay attention to CPU usage and I/O patterns, especially when switching between indexed scans and full sorts.
3. Regressions – Run existing test suites that previously used LIMIT 1 OFFSET 1 to confirm they still pass with the new logic.

Choosing the Right Tool for the Job