Solution with explanation using RANK() in MySQL
This query can  be written using INNER JOIN and/or Sub-query. This solution shows a way of writing it using RANK() function.\n\nStep 1\nWe need to rank the empl
This query can  be written using INNER JOIN and/or Sub-query. This solution shows a way of writing it using `RANK()` function.\n\n**`Step 1`**\nWe need to rank the employees based on their salaries for each department\n\n```\nSELECT\n\td.Name AS Department,\n\te.Name as Employee,\n\te.Salary,\n\t/* Ranking employee based on salary for each department(d.Name)*/\n\tRANK() OVER (PARTITION BY d.Name ORDER BY e.Salary DESC) AS emp_rank #\nFROM employee e\nJOIN department d ON d.Id = e.DepartmentId\n```\n\n**`Step 2`** \nThe ranking above is done in descending order of salary and partitioned by department so the highest salary for each department should always have `rank = 1`. All we need is to just select the ones whose `rank = 1`.\n\nWe can write a CTE(Common Table Expression) for the first step and simply select the necessary columns in second step from the CTE table. \n\nThe full query should look like this:\n```\nWITH employee_rank AS(\n    SELECT\n        d.Name AS Department,\n        e.Name as Employee,\n        e.Salary,\n        RANK() OVER (PARTITION BY d.Name ORDER BY e.Salary DESC) AS emp_rank\n    FROM employee e\n    JOIN department d ON d.Id = e.DepartmentId\n)\n\nSELECT\n    Department,\n    Employee,\n    Salary\nFROM employee_rank\nWHERE emp_rank = 1;\n```\n\n\n**N. B.:** We can also use `DENSE_RANK()` instead of `RANK()` here which will give us the exact same result for this problem as we are only considering the highest salary here.\n\nThis query gives the following runtime and memory usage after successful submission\n```\nRuntime: 505 ms, faster than 88.21% of MySQL online submissions for Department Highest Salary.\nMemory Usage: 0B, less than 100.00% of MySQL online submissions for Department Highest Salary.\n```
['MySQL']