LeetCode 184: Department Highest Salary [SQL]

[@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

[@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

[@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

[@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!

[@isaac5815]

Thanks! Much appreciated.

[@frederikmuller]

Glad it helped!

[@nchou646]

Great video explanation!! thx a lot

[@frederikmuller]

thank you so much!

[@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!

[@amarbhogat3486]

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

[@vaibhavpatharkar6794]

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

[@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.

[@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

[@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

[@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

[@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 ;

[@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

[@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;