Top Two Salaries per Department
In each department, take its two highest salaries, counting equal salaries once. Return the dept, name and salary of everyone who is paid one of those two. A department with only one salary level returns everyone on it. Rows may come in any order.
Example 1
employees
| id | name | dept | salary |
|---|---|---|---|
| 1 | Ada | ops | 5000 |
| 2 | Ben | ops | 5000 |
| 3 | Cy | ops | 4000 |
| 4 | Dee | ops | 3000 |
| 5 | Eli | risk | 6000 |
returns
| dept | name | salary |
|---|---|---|
| ops | Ada | 5000 |
| ops | Ben | 5000 |
| ops | Cy | 4000 |
| risk | Eli | 6000 |
Constraints
- Up to 14 people
- Names are unique
Write it in your browser, against the clock. The model query, with a note on each piece of it, comes after.
More SQL problems
- Worst DrawdownHard
- Between Two PricesBeginner
- Both ConditionsBeginner
- Cheapest FirstBeginner
- Each Symbol OnceBeginner
- How Many SellsBeginner
- No ManagerBeginner
- One SymbolBeginner