LeetCode 184: Department Highest Salary [SQL]

[@avahome5285]

Wow, WHERE IN can matches two columns values, that's so cool. good to know, thanks.

[@frederikmuller]

you can also use CONCAT to combine two columns and then search for matches

[@UmaAndLak]

Coming from SQL Server world, I was not aware of the syntax for the where condition that you typed up. Good to know. Thank you. My attempt to solve the problem is below, with join instead of where clause. It works as well. select d.name AS Department, e.name AS Employee, e.salary AS Salary from Employee e inner join department d on e.departmentId = d.id inner join (SELECT departmentid, max(salary) as ms from employee group by departmentid) le ON le.departmentid = e.departmentId and le.ms = e.salary

[@lts8936]

Hi Frederik, thanks a lot for your thorough explanation. Your solution made a lot of sense to me. However, after I change the order of the two items in where clause from 'WHERE(tb2.Id, tb1.Salary)' to 'WHERE(tb1.Salary, tb2.Id)', it didn'it work. I don't understand why the order matters in this case. Can you please explain? Thanks again!

[@dipeshsaili4468]

However, after I change the order of the two items in where clause from 'WHERE(tb2.Id, tb1.Salary)' to 'WHERE(tb1.Salary, tb2.Id)', it didn'it work. I don't understand why the order matters in this case. Can you please explain? Thanks again!

[@sidawan8279]

with cte as ( select d.name as Department, e.name as Employee, e.salary as Salary, rank() over(partition by e.departmentId order by salary desc) as "ranking" from Employee e left join Department d on e.departmentId = d.id ) select Department, Employee, Salary from cte where ranking = 1

[@amarbhogat3486]

(select departmentid, max(salary) from employee group by departmentid); why does this outputs only 2 salary which are maximum?

[@hallomutter1231]

Hi Frederik, why do you not need to ORDER BY Salary DESC here to get the correct solution. That's the part I'm confused about.

[@frederikmuller]

The problem statement mentions that order of output rows doesn't matter. To get the highest salary we use MAX(), so no need to order by anything.

[@faizraina6]

was it necessary that we include , department id as well in our sub-query . couldn't we just have mentioned select max(salary) and group by department_id . please reply if anyone has the answer

[@ambicadronadula6451]

Group by groups if department id or salary seems to be same.....in this case as we have two persons with highest salary we need to use group by in subquery

[@nchou646]

Great video explanation!! thx a lot

[@frederikmuller]

thank you so much!

[@pranjalmishra2602]

Hey, Could You please explain this? WHERE (Employee.DepartmentId , Employee.Salary) IN ( SELECT DepartmentId, MAX(Salary) FROM Employee GROUP BY DepartmentId )

[@pranjalmishra2602]

specially this part-> (Employee.DepartmentId , Employee.Salary) IN

[@frederikmuller]

the parentheses create a tuple with the two values of DepartmentId and Salary. we're also selecting two columns in the subquery, we're just using the MAX(Salary) instead of Salary. WHERE IN allows us to check whether a row is in a table (in this case whether this tuple is in the table created from the subquery). we're doing all this to make sure we're selecting rows with employees that earn the department highest salary. If we would try to select the employee and the max salary with just one query, we wouldn't be able look up the employee that corresponds to the highest salary.

[@pranjalmishra2602]

@@frederikmuller OHHH!!! GOT IT, Thank you so much

[@isaac5815]

Thanks! Much appreciated.

[@frederikmuller]

Glad it helped!

[@vaibhavpatharkar6794]

Great great great. But i think this question was hard category

[@imdeepu7855]

WITH highest_sal AS ( select employee,salary,department, row_number over (partition by department_id order by salary desc) as r from employee join departemnt on employee.department_id=department.id ) select department,employee,salary from temp_table where r = 1

[@ignaciogonzalez2619]

Does Anyone know if this would work? SELECT Employee.Name, MAX(Employee.Salary) AS Salary , Department.Name AS Department FROM Employee JOIN Department ON Employee.DepartmentID = Department.ID GROUP BY Department ;

[@mritunzaysingh8978]

create table Employees ( id int not null primary key, name nvarchar(20), salary int, department_id int ); create table department ( id int not null primary key, name nvarchar(20) ); insert into Employees values(1,'Joe',70000,1),(2,'Jim',90000,1),(3,'Marry',80000,2),(4,'Sam',60000,2),(5,'Max',90000,1); insert into department values (1,'IT'),(2,'Sales'); select * from Employees; select * from department; with CTE as ( select E.name as emp_name,D.name as dep_name,E.salary as emp_salary,max(E.salary) over(partition by E.department_id order by E.department_id) as rnk from Employees as E join department as D on E.department_id = D.id ) select dep_name,emp_name,emp_salary from CTE where rnk = emp_salary; -- Second Way select dep_name,emp_name,emp_salary from ( select E.name as emp_name,D.name as dep_name,E.salary as emp_salary,max(E.salary) over(partition by E.department_id order by E.department_id) as rnk from Employees as E join department as D on E.department_id = D.id ) A where rnk = emp_salary;

[@sanaayakurup5453]

My solution with window functions select Department.name as Department ,lookup.name as Employee,salary as Salary from(select name,salary, departmentId, dense_rank() over (partition by departmentId order by salary desc) as ranks from Employee)lookup join Department on lookup.departmentId=Department.id where ranks=1