## DEV Community

Retiago Drago

Posted on • Updated on

# Second Highest Salary | LeetCode | MSSQL

## Problem Statement

The structure of the `Employee` table is as follows:

Column Name Type
id int
salary int

Here, the `id` is the primary key of the table and each row contains salary information for an employee.

Our objective is to write an SQL query to report the second highest salary.

Example 1:

id salary
1 100
2 200
3 300

Output:

SecondHighestSalary
200

Example 2:

id salary
1 100

Output:

SecondHighestSalary
null

## Approaches to the Solution

We will explore three approaches, each using different concepts in SQL. We'll explain each method in detail, highlighting their strengths and weaknesses. Additionally, we'll also provide performance results for these solutions.

### 1. Using Window Function and Subquery

The first solution leverages the window function `DENSE_RANK()` and a subquery to return the second highest salary.

``````WITH second_ranking AS (
SELECT
Salary,
DENSE_RANK() OVER (ORDER BY salary DESC) [ranking]
FROM Employee
), result AS (
SELECT TOP 1 Salary
FROM second_ranking
WHERE ranking = 2
)
SELECT (SELECT Salary FROM result) [SecondHighestSalary]
``````

In the first part of the query, we use `DENSE_RANK()` to rank salaries in descending order. We then filter out the second highest salary (where ranking = 2) using a subquery. Although this approach is clear and logical, its performance is not optimal, achieving a runtime of 877ms, beating 14.57% of LeetCode submissions.

### 2. Using Window Function, DISTINCT, and Subquery

The second solution is a variation of the first, with the addition of the `DISTINCT` keyword to ensure that the selected salary is unique.

``````WITH second_ranking AS (
SELECT
Salary,
DENSE_RANK() OVER (ORDER BY salary DESC) [ranking]
FROM Employee
), result AS (
SELECT DISTINCT Salary
FROM second_ranking
WHERE ranking = 2
)
SELECT (SELECT Salary FROM result) [SecondHighestSalary]
``````

The use of `DISTINCT` adds a uniqueness condition but comes with a slight decrease in performance. This query has a runtime of 984ms, beating 8.46% of LeetCode submissions.

### 3. Using Subquery and MAX Function

The third solution uses a combination of subqueries and the `MAX` function to return the second highest salary.

``````SELECT
(
SELECT MAX(salary)
FROM Employee
WHERE salary <> (SELECT MAX(salary) FROM Employee)
) [SecondHighestSalary]
``````

This solution has a much better performance, achieving a runtime of 649ms, beating 58.23% of LeetCode submissions.

## Conclusion

From these three solutions, we learn that in SQL, a more straightforward approach sometimes results in better performance. Using basic SQL functions and operations (like `MAX` and subqueries) can outperform more complex methods involving window functions. Therefore, the third solution is ranked as the best in terms of overall performance on LeetCode.

However, please note that performance can vary significantly based on the specific RDBMS, data distribution, and table size in real-world applications. So it's important to test different methods in your specific environment.

Find the original problem on LeetCode.

For more solutions and tech-related content, connect with me on my Beacons page.

## ranggakd - Link in Bio & Creator Tools | Beacons

@ranggakd | center details summary summary Oh hello there I m a an Programmer AI Tech Writer Data Practitioner Statistics Math Addict Open Source Contributor Quantum Computing Enthusiast details center.

beacons.ai