Second Highest Salary - Solution & Explanation

Q: Is Second Highest Salary easy or hard?

Second Highest Salary is generally classified as a medium difficulty SQL problem. The logic is simple once you understand how to combine aggregation with filtering, but handling duplicates and returning NULL when no second value exists can confuse beginners.

Q: Second Highest Salary Python/Java solution

If salary values are retrieved into application code, iterate once while tracking the largest and second largest distinct numbers. Update these values during each comparison. This algorithm runs in O(n) time with O(1) extra space.

Q: How to solve Second Highest Salary in O(n)?

Use a subquery with MAX aggregation. First compute the maximum salary, then find the maximum salary strictly less than it: SELECT MAX(Salary) FROM Employee WHERE Salary < (SELECT MAX(Salary) FROM Employee). The database performs two scans but avoids sorting, resulting in linear time complexity.

Q: What is the best approach for Second Highest Salary?

The most common solution uses a MAX aggregation with a subquery: SELECT MAX(Salary) FROM Employee WHERE Salary < (SELECT MAX(Salary) FROM Employee). This directly finds the largest salary smaller than the global maximum. It runs in O(n) time because the database performs aggregation scans over the table.

Q: Is Second Highest Salary asked at Google/Amazon/Meta?

Second Highest Salary is a classic SQL interview question used by many companies including Amazon, Google, and other data-focused roles. It tests understanding of SQL aggregation, filtering conditions, and handling duplicate values.

Q: What data structure is used in Second Highest Salary?

The problem primarily uses SQL database operations rather than traditional data structures. It relies on aggregation functions like MAX, filtering conditions, and sorting mechanisms inside the database query engine.

Q: What is the time complexity of Second Highest Salary?

Using the MAX with subquery approach typically runs in O(n) time because the database scans the table to compute the maximum values. A sorting-based approach using ORDER BY requires O(n log n) time due to the sorting operation.

MediumDatabase7 min readAsked at: Amazon, Microsoft, Apple +16

Practice this problem

Problem Statement

Table: Employee

+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| salary      | int  |
+-------------+------+
id is the primary key (column with unique values) for this table.
Each row of this table contains information about the salary of an employee.

Write a solution to find the second highest distinct salary from the Employee table. If there is no second highest salary, return null (return None in Pandas).

The result format is in the following example.

Example 1:

Input: 
Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+
Output: 
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200                 |
+---------------------+

Example 2:

Input: 
Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
+----+--------+
Output: 
+---------------------+
| SecondHighestSalary |
+---------------------+
| null                |
+---------------------+

Approach Overview

Problem Overview: The Employee table contains salaries for multiple employees. Your task is to return the second highest salary. If a second distinct salary does not exist, the query should return NULL. The challenge is handling duplicates and ensuring the result reflects the second distinct value.

Approach 1: MAX with Subquery (O(n) time, O(1) space)

This approach scans the table twice using SQL aggregation. First, compute the maximum salary using MAX(Salary). Then run a second query that finds the maximum salary strictly less than that value. The key insight is that the second highest salary must be the largest value smaller than the overall maximum. SQL handles the comparison efficiently using a subquery: SELECT MAX(Salary) FROM Employee WHERE Salary < (SELECT MAX(Salary) FROM Employee). Database engines typically perform table scans for these aggregations, resulting in O(n) time complexity with constant extra space. This method is widely used in interviews because it clearly expresses the logical definition of "second highest." It relies entirely on SQL aggregation and comparison operations from database fundamentals.

Approach 2: ORDER BY with LIMIT/OFFSET (O(n log n) time, O(1) space)

This strategy sorts salaries in descending order and selects the second distinct value. The query uses SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 1 OFFSET 1. Sorting ensures the highest salary appears first and the second highest appears next. The DISTINCT keyword removes duplicates so repeated salaries do not interfere with the ranking. Sorting typically costs O(n log n) time in the database engine. The advantage is simplicity—this pattern reads like a ranking query and is easy to modify for the third or fourth highest salary. This approach demonstrates practical use of SQL ordering and pagination features.

Approach 3: Language-Specific Processing After Fetch (O(n) time, O(1) space)

In application code (Java, JavaScript, Python, or C), you can fetch salaries and compute the result manually. Iterate through all values while tracking the highest and second highest distinct numbers. Each iteration compares the current salary with the tracked values and updates them accordingly. This requires only a single pass through the data, giving O(n) time complexity and constant memory. The approach highlights basic algorithm reasoning and works well when salary data is already loaded in memory.

Recommended for interviews: The MAX with subquery approach is the most common expectation for SQL interviews. It shows you understand aggregation, filtering, and query composition. Sorting with ORDER BY is also valid but less optimal because of the sorting cost. Implementing the logic in application code demonstrates algorithmic thinking, but interviewers typically prefer a pure SQL solution when the problem is labeled as a database question.

Approach 1: Using SQL to Identify Second Highest Salary

This approach involves directly querying the database to retrieve the second highest distinct salary. The SQL query utilizes the MAX function in a subquery to exclude the highest salary from consideration, thus efficiently targeting the second highest.

This C solution uses sqlite3 to connect to a database and execute a SQL query that selects the second highest salary from the Employee table. The MAX function is used within a sub-query to find and exclude the highest salary, thereby obtaining the second highest.

Code

C Python

Python

Complexity

Time Complexity: O(n) - Iterating through database rows
Space Complexity: O(1) - Constant additional space

Try this approach in the editor →

Approach 2: Using Language-Specific Features to Compute Second Highest Salary

This approach does not rely on SQL but instead employs language-specific paradigms, such as sorting or managing unique salary values in a list or array, to identify the second highest distinct salary.

This Java solution uses JDBC to connect to the SQLite database, retrieves all salaries, and uses a TreeSet to store unique salaries in descending order. The second highest salary, if available, is then retrieved by skipping the first element (highest).

Code

Java JavaScript

Java

JavaScript

Complexity

Time Complexity: O(n log n) - Due to TreeSet insertion
Space Complexity: O(n) - Storage for unique salaries

Try this approach in the editor →

Approach 3: Use Sub Query and LIMIT

Code

Python MySQL

Python

MySQL

Try this approach in the editor →

Approach 4: Use `MAX()` function

Code

MySQL

Try this approach in the editor →

Approach 5: Use `IFNULL()` and window function

Code

MySQL

Try this approach in the editor →

Complexity Comparison

Approach	Complexity
Using SQL to Identify Second Highest Salary	Time Complexity: O(n) - Iterating through database rows Space Complexity: O(1) - Constant additional space
Using Language-Specific Features to Compute Second Highest Salary	Time Complexity: O(n log n) - Due to TreeSet insertion Space Complexity: O(n) - Storage for unique salaries
Use Sub Query and LIMIT	—
Use `MAX()` function	—
Use `IFNULL()` and window function	—

Detailed Complexity Analysis

Approach	Time	Space	When to Use
MAX with Subquery	O(n)	O(1)	Best general SQL solution; avoids sorting and clearly expresses second-highest logic
ORDER BY with LIMIT/OFFSET	O(n log n)	O(1)	Useful when ranking or pagination queries are already required
Single Pass in Application Code	O(n)	O(1)	When salaries are processed outside the database in Java, Python, or JavaScript

Video Solution

LeetCode 176: Second Highest Salary [SQL] • Frederik Müller • 23,449 views views

Watch 9 more video solutions →

Frequently Asked Questions

Is Second Highest Salary easy or hard?

Second Highest Salary is generally classified as a medium difficulty SQL problem. The logic is simple once you understand how to combine aggregation with filtering, but handling duplicates and returning NULL when no second value exists can confuse beginners.

Second Highest Salary Python/Java solution

If salary values are retrieved into application code, iterate once while tracking the largest and second largest distinct numbers. Update these values during each comparison. This algorithm runs in O(n) time with O(1) extra space.

How to solve Second Highest Salary in O(n)?

Use a subquery with MAX aggregation. First compute the maximum salary, then find the maximum salary strictly less than it: SELECT MAX(Salary) FROM Employee WHERE Salary < (SELECT MAX(Salary) FROM Employee). The database performs two scans but avoids sorting, resulting in linear time complexity.

What is the best approach for Second Highest Salary?

The most common solution uses a MAX aggregation with a subquery: SELECT MAX(Salary) FROM Employee WHERE Salary < (SELECT MAX(Salary) FROM Employee). This directly finds the largest salary smaller than the global maximum. It runs in O(n) time because the database performs aggregation scans over the table.

Is Second Highest Salary asked at Google/Amazon/Meta?

Second Highest Salary is a classic SQL interview question used by many companies including Amazon, Google, and other data-focused roles. It tests understanding of SQL aggregation, filtering conditions, and handling duplicate values.

What data structure is used in Second Highest Salary?

The problem primarily uses SQL database operations rather than traditional data structures. It relies on aggregation functions like MAX, filtering conditions, and sorting mechanisms inside the database query engine.

What is the time complexity of Second Highest Salary?

Using the MAX with subquery approach typically runs in O(n) time because the database scans the table to compute the maximum values. A sorting-based approach using ORDER BY requires O(n log n) time due to the sorting operation.

Ready to solve this problem?

Practice Second Highest Salary with our built-in code editor and test cases.

Practice on FleetCode

Combine Two Tables

Nth Highest Salary

Problem Info

DifficultyMedium

Acceptance42.2%

Approaches5

Reading time7 min

Asked at

Amazon Microsoft Apple Meta Oracle

Practice this problem

Open in Editor

Problem Statement

Approach Overview

Approach 1: Using SQL to Identify Second Highest Salary

Code

Complexity

Approach 2: Using Language-Specific Features to Compute Second Highest Salary

Code

Complexity

Approach 3: Use Sub Query and LIMIT

Code

Approach 4: Use `MAX()` function

Code

Approach 5: Use `IFNULL()` and window function

Code

Complexity Comparison

Detailed Complexity Analysis

Video Solution

Frequently Asked Questions

Related Problems

Ready to solve this problem?

Problem Info

Table of Contents