The second-highest salary in each department
Your task
For each department, return the employee with the second-highest salary. Give department, name and salary, ordered by department name.
Departments with fewer than two recorded salaries simply do not appear.
Tables
department
| idINTEGER | nameTEXT |
|---|---|
| 1 | Engineering |
| 2 | Sales |
| 3 | Design |
| 4 | Research |
employee
More rows not shown
| idINTEGER | nameTEXT | salaryINTEGER | department_idINTEGER | manager_idINTEGER | hired_onTEXT |
|---|---|---|---|---|---|
| 1 | Ada | 180000 | 1 | NULL | 2021-03-01 |
| 2 | Grace | 165000 | 1 | 1 | 2022-06-15 |
| 3 | Linus | 140000 | 1 | 1 | 2023-01-09 |
| 4 | Mel | 120000 | 2 | NULL | 2020-11-20 |
| 5 | Kay | 120000 | 2 | 4 | 2023-04-02 |
| 6 | Ivan | NULL | 2 | 4 | 2024-02-17 |