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
'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 :)
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;
# 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)
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
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!
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
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;
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
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?
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
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 ;
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.
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;
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
Checkout the Big Data course details here: trendytech.in/?referrer=youtube_lc5
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
use dense_rank over rank as it will allow more than 1 highest salary in each dept
'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 :)
Finished the whole playlist. Really helpful. I've learned How to approach any SQL problem.
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;
More Useful Sir. Kindly upload these kinds of problems continuously.
Please bring few more problem statements showing the usage of full outer join & Cartesian product as well
Please upload more videos to this playlist
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
Thanks @ helpful !!!
Glad that it was helpfull
sir waiting for next session
Very soon
# 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)
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
why are we using department id in sub-query . why do we need to include it , can't we just use max(salary) only
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!
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
Window functions are not always cost effective
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;
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
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
use window functions
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?
Yes. It works
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
@@dikshagupta2795 Thanks
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 ;
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.
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;
pls solve prob 627
Noted :) will cover that in the upcoming sessions
@@sumitmittal07 thank you sir
@@Iyersukz6 you need to use update with case statement in that problem
Looks complicated querry
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