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
Understanding the Visualization
1
Input Table
Employee table with id and salary
2
Find Distinct
Get unique salaries ordered desc
3
Select Second
Return second highest or null
Key Takeaway
🎯 Key Insight: Use DISTINCT with ORDER BY and OFFSET to handle ranking with proper null handling
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code