Finding the 2nd Highest Salary of Employees in SQL

Finding the 2nd highest salary in an employee database is a common SQL problem that can be approached in multiple ways. This article explores several methods, using various SQL constructs and functions, with detailed examples and explanations.

Method 1: Using Subquery with LIMIT and OFFSET

One straightforward method is to use a subquery with `LIMIT` and `OFFSET` to fetch the 2nd highest salary.

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

Explanation:

  1. `SELECT DISTINCT salary` : Selects unique salaries to avoid duplicates.
  2. `FROM employees` : Specifies the table from which to fetch data.
  3. `ORDER BY salary DESC` : Orders the salaries in descending order.
  4. `LIMIT 1 OFFSET 1` : Skips the highest salary and fetches the next one.

Method 2: Using Subquery with MAX and NOT IN

This method uses a subquery to find the maximum salary and then finds the maximum salary that is not the highest.

SELECT MAX(salary) AS second_highest_salary
FROM employees
WHERE salary NOT IN (SELECT MAX(salary) FROM employees);

Explanation:

  1. `SELECT MAX(salary)` : Finds the maximum salary.
  2. `FROM employees` : Specifies the table.
  3. `WHERE salary NOT IN (SELECT MAX(salary) FROM employees)` : Excludes the highest salary from the results.

Method 3: Using Subquery with Ranking Function (ROW_NUMBER)

Using window functions like `ROW_NUMBER` can efficiently rank salaries and find the 2nd highest.

WITH RankedSalaries AS (
    SELECT salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
    FROM employees
)
SELECT salary
FROM RankedSalaries
WHERE rank = 2;

Explanation:

  1. `WITH RankedSalaries AS` : Creates a Common Table Expression (CTE).
  2. `SELECT salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank` : Assigns a rank to each salary.
  3. `FROM employees` : Specifies the table.
  4. `SELECT salary FROM RankedSalaries WHERE rank = 2` : Selects the salary with a rank of 2.

Method 4: Using DENSE_RANK for Handling Duplicate Salaries

`DENSE_RANK` can be used instead of `ROW_NUMBER` to handle cases where there are duplicate salaries.

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

Explanation:

  1. `DENSE_RANK` : Provides continuous ranking even if there are ties, ensuring the correct second highest even with duplicate salaries.

Method 5: Using Nested Subqueries

This method involves nested subqueries to first find the unique salaries and then the 2nd highest.

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

Explanation:

  1. `SELECT MAX(salary)` : Finds the highest salary less than the maximum salary.
  2. `WHERE salary < (SELECT MAX(salary) FROM employees)` : Ensures the salary is less than the highest salary.

Method 6: Using Common Table Expressions (CTEs)

CTEs can also be used to simplify complex queries, making them more readable.

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

Explanation:

  1. `WITH UniqueSalaries AS` : First CTE to select unique salaries.
  2. `ROW_NUMBER() OVER (ORDER BY salary DESC)` : Assigns ranks to these unique salaries.
  3. `SELECT salary FROM RankedSalaries WHERE rank = 2` : Selects the salary ranked second.

Conclusion

Finding the 2nd highest salary in SQL can be approached in multiple ways, each with its benefits depending on the database’s specific needs and the complexity of the data. Whether using subqueries, window functions, or CTEs, SQL provides powerful tools to retrieve precise results efficiently.

Finding the 2nd Highest Salary of Employees in SQL

Published on 20-Jun-2024 16:40:55

You may also like this!