184. Department Highest Salary

[QuXiangjie]

Question medium Question hard

SELECT Department, Employee, Salary
FROM (
    SELECT 
        d.name AS Department,
        e.name AS Employee,
        e.salary AS Salary,
        DENSE_RANK() OVER (PARTITION BY d.name ORDER BY Salary DESC) AS rnk
    FROM Employee e
    JOIN Department d
    ON e.departmentId = d.id
) AS rnk_tbl
WHERE rnk <= 3;

Dense_Rank() over(Partition by d.name Order by Salary Desc) as rnk 使用partition by 分组，分组后按照salary 排序 取数可以使用添加1列排序来进行实现

here is the way to use subtable with subtable as( ), subtable 1 as( ) select....

[QuXiangjie]

SELECT d.name AS Department
      ,e.name AS Employee
      ,e.salary AS Salary
FROM Employee AS e
 JOIN Department AS d ON e.departmentId=d.id
WHERE (departmentId,salary) IN(SELECT departmentId,MAX(salary) 
                               FROM Employee
                               GROUP BY departmentId)