Interview Questions/SQL/Second Highest Salary Per Department

Second Highest Salary Per Department

Preview mode. Log in to edit, run, submit, and save progress.

Medium

Second Highest Salary Per Department

You are given a table of staff members. Each staff member has a name, a department, and a salary. Write a SQL query to find the second highest distinct salary in each department. If a department does not have at least two distinct salary values, it should not appear in the result. Return the result ordered by department. Table: Staff

Column Name	Type	Description
id	INT	Primary key
name	VARCHAR	Staff member name
dept	VARCHAR	Department name
salary	INT	Annual salary

Examples

Example 1

In Eng, the max salary is 100000 so the second highest is 90000. In Sales, both Dave and Eve earn 70000 (the max), so the second distinct salary is 60000.

Approach hint

Start with a simple approach, explain the trade-off, then move toward a cleaner or more scalable solution.

Common mistake

Skipping assumptions, edge cases, or trade-offs can make an otherwise good answer feel incomplete.

query.sql