Leetcode 185: Department Top Three Salaries

Join Employee and Department tables by DepartmentId. In WHERE clause we need to filter to show only salaries higher than a subquery result. In that subquery we will try to return the third highest distinct salary. Do that subquery by listing Salaries of Employees ordered by Salary Descending and limit to 1 with offset 2. The problem is that departments with less than 3 employees will return an empty result set for that subquery, so we can make it return 0 by wrapping it in IFNULL(). Finally I order by department name and salary descending to make the output look nicer. I think it is also possible to solve this problem with the subquery logic returning a count distinct of salaries.

Here is the solution runnable online:
https://paiza.io/projects/BIfLpKxnZbaEOIjIk9f9EQ?language=mysql
And here is the code gist:

This entry was posted in programming and tagged , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published.