Problem · Hard · 30 min
Top two salaries in each team
- Hard
- Window functions
A company keeps its staff in the table staff.
| Column | Meaning |
|---|---|
id |
The number of the person. It is unique. |
name |
The name of the person. |
team |
The team of the person. |
salary |
The monthly salary in euros, as a whole number. |
Inside each team, look at the different salary values, from the highest to the lowest. Write one query that returns every person whose salary is one of the two highest different salary values of their team.
- If several people of a team have the same salary, they all have the same position. For example, if a team pays 5000, 4000, 4000 and 3000, the two highest values are 5000 and 4000. Then three people are in the result.
- A team with only one salary value returns all its people.
- The columns are
team,nameandsalary. - Sort the rows by
team, then bysalarywith the highest first, then byname.
Tasks
2 to do
Task 1, not checked yet. Return the right rows for the example data.
Task 2, not checked yet. Return the right rows for other data that you cannot see.
Task 1 of 2
Return the right rows for the example data.