Second Highest Salary II - Problem

Given a table employees with employee information including their ID, salary, and department, write a SQL query to find the employees who earn the second-highest salary in each department.

Key Requirements:

  • Find the second-highest salary within each department
  • If multiple employees have the same second-highest salary, include all of them
  • If a department has fewer than 2 distinct salary levels, exclude it from results
  • Return results ordered by emp_id in ascending order

Table Schema

employees
Column Name Type Description
emp_id PK int Unique employee identifier (primary key)
salary int Employee's salary amount
dept varchar Department name where employee works
Primary Key: emp_id
Note: Each row represents one employee with their salary and department information

Input & Output

Example 1 — Multiple Departments with Ties
Input Table:
emp_id salary dept
1 70000 Sales
2 80000 Sales
3 80000 Sales
4 90000 Sales
5 55000 IT
6 65000 IT
7 65000 IT
8 50000 Marketing
9 55000 Marketing
10 55000 HR
Output:
emp_id dept
2 Sales
3 Sales
5 IT
8 Marketing
💡 Note:

Sales: Highest is 90000 (emp_id 4), second-highest is 80000 (both emp_id 2 and 3 included due to tie)

IT: Highest is 65000 (emp_id 6,7), second-highest is 55000 (emp_id 5)

Marketing: Highest is 55000 (emp_id 9), second-highest is 50000 (emp_id 8)

HR: Only one salary level (55000), so no second-highest exists - excluded from results
Example 2 — Department with Insufficient Salary Levels
Input Table:
emp_id salary dept
1 100000 Engineering
2 100000 Engineering
3 100000 Engineering
4 75000 Finance
5 80000 Finance
Output:
emp_id dept
4 Finance
💡 Note:

Engineering: All employees have the same salary (100000), so there's only one distinct salary level. No second-highest exists - excluded from results.

Finance: Highest is 80000 (emp_id 5), second-highest is 75000 (emp_id 4).
Example 3 — Empty Result
Input Table:
emp_id salary dept
1 90000 Sales
2 60000 IT
Output:
emp_id dept
💡 Note:

Each department has only one employee, so neither department has a second-highest salary. The result is empty.

Constraints

  • 1 ≤ emp_id ≤ 10^4
  • 1 ≤ salary ≤ 10^6
  • dept is a non-empty string
  • Each emp_id is unique

Visualization

Tap to expand
Second Highest Salary II INPUT emp_id salary dept_id 1 80000 D1 2 70000 D1 3 70000 D1 4 90000 D2 5 85000 D2 6 85000 D2 7 60000 D3 Departments: D1 D2 D3 Find 2nd highest salary per department ALGORITHM STEPS 1 DENSE_RANK() Rank salaries within each dept (DESC order) 2 PARTITION BY Group by department_id for ranking 3 Filter rank = 2 Keep only 2nd highest salary employees 4 ORDER BY emp_id Sort results ascending WITH ranked AS ( SELECT *, DENSE_RANK() OVER(PARTITION BY dept ORDER BY salary DESC) ) SELECT * WHERE rk = 2 ORDER BY emp_id FINAL RESULT emp_id salary dept 2 70000 D1 3 70000 D1 5 85000 D2 6 85000 D2 Results Explained: D1: emp 2,3 tie at 70K (2nd) D2: emp 5,6 tie at 85K (2nd) D3: excluded (only 1 salary) OK 4 employees found Ordered by emp_id ASC Key Insight: DENSE_RANK() handles ties correctly - multiple employees with the same salary get the same rank. Unlike RANK(), DENSE_RANK() has no gaps, so rank=2 always means second-highest distinct salary. Departments with only one distinct salary level are automatically excluded when filtering for rank=2. TutorialsPoint - Second Highest Salary II | Optimal Solution (Window Function)

Related Problems

Asked in
Amazon 23 Google 18 Microsoft 15 Facebook 12
23.4K Views
High Frequency
~12 min Avg. Time
847 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