Second Highest Salary solution and explanation
Infosys
Deloitte
TCS
Google
9 more videos on this problem
LeetCode 176: Second Highest Salary [SQL]Frederik Müller · 7:19 · 23K views
47. Second Highest Salary | SQL Interview Questions and AnswersStart Practicing · 2:25 · 11K views
LeetCode Medium 176 Amazon Interview SQL Question with Detailed ExplanationEveryday Data Science · 9:44 · 8K views
Second Highest Salary | Leetcode 176 | Crack SQL Interviews in 50 Qs #mysql #leetcodeLearn With Chirag · 4:58 · 8K views
Learn SQL - SQL Interview Question - Second Highest SalaryNeetCode · 6:14 · 6K views
Leetcode 176 - Second Highest Salary - Python Solution | FAANG InterviewsEveryday Data Science · 8:44 · 3K views
Leetcode Problem 176. Second Highest Salary | Leetcode SQL Interview Problem with SolutionThe Engineer Guy 2.0 · 9:25 · 1K views
176. Second Highest Salary | LeetCode MySQL Solution [MEDIUM]Code with Carter · 5:20 · 865 views
Leetcode 176 | Second Highest Salary | FAANG Easy SQL InterviewBig Tech Mindset · 2:58 · 574 views
LeetCode 176: Second Highest Salary (SQL Medium)Big Tech Mindset · 8:31 · 383 views
The problem
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.
import sqlite3
conn = sqlite3.connect('company.db')
cursor = conn.cursor()
query = """
SELECT MAX(salary) AS SecondHighestSalary FROM Employee
WHERE salary < (SELECT MAX(salary) FROM Employee);
"""
cursor.execute(query)
result = cursor.fetchone()[0]
conn.close()
print(result if result is not None else 'None')
#include <stdio.h>
#include <sqlite3.h>
int main() {
sqlite3 *db;
sqlite3_open("company.db", &db);
const char *sql = "SELECT MAX(salary) AS SecondHighestSalary FROM Employee WHERE salary < (SELECT MAX(salary) FROM Employee);";
// Execute SQL and handle results
sqlite3_close(db);
return 0;
}This Python solution uses sqlite3 to execute a SQL query that selects the second highest salary from the Employee table. It uses a sub-query to fetch the maximum salary and exclude it to find the second maximum.
Time Complexity: O(n) - Iterates through rows in the employee table
Space Complexity: O(1) - Uses constant space to store results
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.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.*;
public class SecondHighestSalary {
public static void main(String[] args) {
String url = "jdbc:sqlite:company.db";
try (Connection conn = DriverManager.getConnection(url);
Statement stmt = conn.createStatement()) {
String sql = "SELECT salary FROM Employee";
ResultSet rs = stmt.executeQuery(sql);
Set<Integer> salaries = new TreeSet<>(Collections.reverseOrder());
while (rs.next()) {
salaries.add(rs.getInt("salary"));
}
Integer secondHighest = salaries.stream().skip(1).findFirst().orElse(null);
System.out.println(secondHighest != null ? secondHighest : "None");
} catch (Exception e) {
e.printStackTrace();
}
}
}
const sqlite3 = require('sqlite3').verbose();
let db = new sqlite3.Database('company.db', (err) => {
if (err) console.error(err.message);
});
const getSecondHighestSalary = () => {
db.serialize(() => {
db.all(`SELECT salary FROM Employee`, [], (err, rows) => {
if (err) {
throw err;
}
let salaries = rows.map(row => row.salary);
let uniqueSalaries = Array.from(new Set(salaries)).sort((a, b) => b - a);
console.log(uniqueSalaries[1] || 'None');
});
});
};
getSecondHighestSalary();
db.close();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).
Time Complexity: O(n log n) - Due to TreeSet insertion
Space Complexity: O(n) - Storage for unique salaries
Approach 3: Use Sub Query and LIMIT
# Write your MySQL query statement below
SELECT
(
SELECT DISTINCT salary
FROM Employee
ORDER BY salary DESC
LIMIT 1, 1
) AS SecondHighestSalary;Approach 4: Use `MAX()` function
# Write your MySQL query statement below
SELECT MAX(salary) AS SecondHighestSalary
FROM Employee
WHERE salary < (SELECT MAX(salary) FROM Employee);Approach 5: Use `IFNULL()` and window function
# Write your MySQL query statement below
WITH T AS (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rk FROM Employee)
SELECT (SELECT DISTINCT salary FROM T WHERE rk = 2) AS SecondHighestSalary;Complexity comparison
| Approach | Complexity |
|---|---|
| Using SQL to Identify Second Highest Salary | Time Complexity: O(n) - Iterates through rows in the employee table |
| Using Language-Specific Features to Compute Second Highest Salary | Time Complexity: O(n log n) - Due to TreeSet insertion |
| Use Sub Query and LIMIT | – |
| Use `MAX()` function | – |
| Use `IFNULL()` and window function | – |
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 |
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.