Second Highest Salary - Problem

Given an Employee table with employee salaries, write a SQL query to find the second highest distinct salary.

If there is no second highest salary, return null.

Table Structure:

  • id - Primary key (integer)
  • salary - Employee salary (integer)

The query should handle edge cases where there are fewer than 2 distinct salary values.

Table Schema

Employee
Column Name Type Description
id PK int Primary key for each employee
salary int Employee salary amount
Primary Key: id
Note: Each row represents one employee's salary information

Input & Output

Example 1 — Basic Case
Input Table:
id salary
1 100
2 200
3 300
Output:
SecondHighestSalary
200
💡 Note:

The distinct salaries in descending order are: 300, 200, 100. The second highest is 200.
Example 2 — No Second Highest
Input Table:
id salary
1 100
Output:
SecondHighestSalary
💡 Note:

There is only one salary value (100), so there is no second highest salary. Return null.
Example 3 — Duplicate Salaries
Input Table:
id salary
1 100
2 200
3 200
Output:
SecondHighestSalary
100
💡 Note:

The distinct salaries are: 200, 100. Even though 200 appears twice, the second highest distinct salary is 100.

Constraints

  • 1 ≤ Employee.id ≤ 1000
  • 1 ≤ Employee.salary ≤ 100000
  • All employee IDs are unique

Visualization

Tap to expand
Second Highest Salary INPUT Employee Table Id Salary 1 100 2 200 3 300 4 300 Distinct Salaries: 100 200 300 Goal: Find 2nd highest distinct salary value Return NULL if none exists ALGORITHM STEPS 1 SELECT DISTINCT Remove duplicate salaries 2 ORDER BY DESC Sort salaries high to low 3 LIMIT / OFFSET Skip 1st, take 2nd row 4 Handle NULL Wrap in subquery for NULL SELECT (SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 1 OFFSET 1) AS SecondHighestSalary; FINAL RESULT Processing Flow: Sorted DESC: 300 1st (skip) 200 2nd [OK] SecondHighestSalary 200 Edge Case: If only 1 distinct salary Result: NULL Key Insight: Using a subquery wrapper ensures NULL is returned when no second highest salary exists. DISTINCT eliminates duplicates, ORDER BY DESC + LIMIT 1 OFFSET 1 efficiently retrieves the second highest value. Time Complexity: O(n log n) for sorting. TutorialsPoint - Second Highest Salary | Optimal Solution

Related Problems

Asked in
Amazon 23 Google 18 Microsoft 15
78.5K Views
High Frequency
~12 min Avg. Time
2.2K Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen