Very Famous SQL Interview Question | Department Highest Salary

[@sumitmittal07]

Checkout the Big Data course details here: trendytech.in/?referrer=youtube_lc5

[@arunsundar3739]

'where' clause with more than one column, along with 'in' clause is a combination i am learning first time, very interesting, gaining more confidence in sql questions now :)

[@rokibchi]

Finished the whole playlist. Really helpful. I've learned How to approach any SQL problem.

[@sonumahto1364]

select Department, Employee, Salary from ( select d.name as Department,e.name as Employee,e.salary, rank() over (partition by departmentid order by salary desc) rnk from Employee e join department d on e.departmentid=d.id ) x where x.rnk=1 It is more optimised

[@himanchalchandra6202]

use dense_rank over rank as it will allow more than 1 highest salary in each dept

[@angelnadar6451]

Thanks @ helpful !!!

[@sumitmittal07]

Glad that it was helpfull

[@RITURAJRANJAN-st7dv]

Thanks for the wonderful solution Sir. I have tried to solve in a different way. select Department,Employee,salary from (select Department,Employee,salary,dense_rank() over(partition by Department order by salary desc) as denserank from (select e.name as Employee,salary,d.name as Department from Employee e join Department d on e.departmentId = d.id) temp) temp1 where denserank=1;

[@ashitosh501]

sir waiting for next session

[@sumitmittal07]

Very soon

[@HARSHRAJ-gp6ve]

with cte as( select Employee.name,Employee.salary,Department.name as dept_name FROM Employee JOIN Department ON Employee.departmentId=Department.id ),cte1 as( select cte.*,DENSE_RANK()OVER(PARTITION BY dept_name ORDER BY salary DESC) as r1 FROM cte ) select dept_name as Department,name as Employee,salary as Salary FROM cte1 where r1=1;

[@vijaybabukommuri5125]

# Write your MySQL query statement below select B.name as Department, A.name as Employee, A.salary as Salary from ( select name,departmentId,salary, DENSE_RANK() OVER (PARTITION BY departmentId ORDER BY salary desc) as rn from Employee) as A left join Department B on A.departmentId=B.id where A.rn=1 ; But your approach is much better and straightforward, Unnecessarily used the DENSE_RANK and made it more complex SELECT B.name, A.name, A.salary FROM Employee A JOIN Department B ON A.departmentId=B.id WHERE (A.departmentId,A.salary) in (SELECT departmentId, MAX(salary) FROM Employee GROUP BY departmentId)

[@venkataram6460]

Please upload more videos to this playlist

[@adnanmulla6640]

Alternate way: select Department,Employee,Salary from (select d.name as Department, e.name as Employee, dense_rank() over (partition by d.name order by salary desc) as Sal, Salary from Employee e inner join Department d on e.departmentId=d.id)x where x.Sal=1

[@sathiyanr3992]

More Useful Sir. Kindly upload these kinds of problems continuously.

[@subhajitroy5850]

Please bring few more problem statements showing the usage of full outer join & Cartesian product as well

[@sukanyaiyer2671]

pls solve prob 627

[@sumitmittal07]

Noted :) will cover that in the upcoming sessions

[@sukanyaiyer2671]

@@sumitmittal07 thank you sir

[@lucifieramit1]

@@sukanyaiyer2671 you need to use update with case statement in that problem

[@Foodie_Cooking_Loverz]

Looks complicated querry

[@lucifieramit1]

select temp.name as Department,temp.employeename as Employee ,temp.salary as Salary from (select d.name,e.name as employeename,salary,dense_rank() over ( partition by d.name order by salary desc ) as dr from employee e join department d on e.departmentId=d.id) temp where temp.dr=1

[@thomsondcruz5456]

Window functions are not always cost effective

[@florincopaci6821]

Thank you for all the videos! can you please please do a video about leetcode sql question 2153-number of passengers in each bus 2?Please.Thank you

[@faizraina6]

why are we using department id in sub-query . why do we need to include it , can't we just use max(salary) only

[@razeenahmed7334]

No, we can't use only max(salary) in the sub-query, because that will return only one maximum salary, but here we want to retrieve max salaries for both of the departments!

[@vishwamgupta1329]

WITH Solution as (SELECT Employee.name as Employee, Employee.salary as Sal ,Department.name as Department, DENSE_RANK() OVER(PARTITION BY Department.name ORDER BY Employee.salary DESC) as re FROM Employee INNER JOIN Department ON Employee.departmentID=Department.id) SELECT Department, Employee ,Sal as Salary FROM Solution WHERE re=1; #This one also worked using Dense rank window function

[@103himajapoluri6]

Can anybody help with identifying what is wrong in this query select d.name as Department,e1.name as Empolyee ,max(salary) as Salary from employee e1,department d where e1.departmentId = d.id group by d.name ;

[@ShivaKumar-dj8bj]

This will not work because here in each department for each employee there will be only one row for salary...your query gives result department, employee name and the highest salary of him, here there is only one row so basically no aggregation will happen considering there is only one row for each employee in each department. the logic is you need to first partition the data department wise then order the data by salary in descending order for each department and get the first row in each partitioned data set. you can use either Row number or dense rank to achieve this....check once.

[@karthikbs8457]

Sir I saw all the previous 5 video. This problem I paused and solved the problem on my own. Thanks. But I used Partition method involving 3 select statements. SELECT temp1.Department,temp1.name as Employee,temp1.Salary FROM( SELECT *, RANK() OVER(PARTITION by temp.departmentId ORDER BY temp.Salary DESC) AS rnk FROM( SELECT e.*,d.name as "Department" FROM Employee e INNER JOIN Department d ON e.departmentId=d.id) temp ) temp1 WHERE temp1.rnk=1;

[@ShivaKumar-dj8bj]

for this highest salary rank will work but if you want to get nth highest salary you need to use dense rank...correct me if I'm wrong

[@slyroy7562]

Select * from (Select employee.department, employee.name employee, salary, dense_rank() over(partition by employee.department order by salary desc) as r from employee, department where employee. Id = department. Id) where r=1; Will this works?

[@sukanyaiyer2671]

Yes. It works

[@dikshagupta2795]

There are some changes I have made and after that the query gives expected results: select Department, Employee, Salary from ( Select Department.name as Department, employee.name as Employee, Salary, dense_rank() over(partition by employee.departmentId order by salary desc) as r from employee join Department on employee.departmentId = Department.id ) as r where r = 1

[@slyroy7562]

@@dikshagupta2795 Thanks

[@dikshagupta2795]

Hello Sir, If we are using sql server then what will be the alternative for "WHERE (departmentId, salary)" as we can't use two columns in WHERE in sql server

[@AnweshDash]

use window functions