[MySQL] Find the highest salary with `rank` function
We can either group table by DepartmentId and get the highest salary with max(salary), or use window function rank.  sql SELECT department, employee, salary FRO
We can either group table by `DepartmentId` and get the highest salary with `max(salary)`, or use window function `rank`. 

```sql
SELECT department, employee, salary
FROM ( SELECT a.name AS employee
        , b.name AS department
        , salary
        , RANK() OVER (PARTITION BY b.name ORDER BY a.salary DESC) AS dr
    FROM employee a JOIN department b ON a.departmentid = b.id ) tmp
    WHERE dr = 1
```
[]