L&T SQL Interview Problem | Print Highest and Lowest Salary Employees in Each Department

[@anamikajaiswal9423]

select distinct dep_id, max(salary) over(partition by dep_id order by dep_id) as max_salary_emp, min(salary) over(partition by dep_id order by dep_id) as min_salary_emp from employee

[@abhinavmahajan448]

Thanks for the video. Solved via inner join approach select a.dep_id,a.emp_max,b.emp_min from (select dep_id, emp_name as emp_max , dense_rank() over (partition by dep_id order by salary desc) as max_rk from employee) a inner join (select dep_id, emp_name as emp_min ,dense_rank() over (partition by dep_id order by salary asc) as min_rk from employee) b on a.dep_id = b.dep_id where a.max_rk=1 and b.min_rk = 1

[@sanyamgarg8288]

I also did via this method.

[@GowthamR-ro2pt]

Thanks for the video Ankit, sharing my approach.. 😁 with cte as (select emp_name,dep_id,salary,ROW_NUMBER() over (partition by dep_id order by salary desc)rn from employeez), cte1 as (select dep_id, case when rn = 1 then emp_name end Max_sal, case when rn = 2 then emp_name end Min_sal from cte) select dep_id,max(Max_sal)Maximum,max(Min_sal)Minimum from cte1 group by dep_id

[@sahilummat8555]

Hello Ankit Sir Another approach using row_number with cte as ( select *, ROW_NUMBER()over(partition by dep_id order by salary asc) as min_Sal, ROW_NUMBER()over(partition by dep_id order by salary desc) as maxx_Sal from employee) select dep_id, max(case when min_Sal =1 then emp_name else null end )as emp_with_min_sal ,max(case when maxx_Sal =1 then emp_name else null end) as emp_with_max_sal from cte group by dep_id

[@bickramjitbasu5751]

with highest_lowest_sal_cte as (select * , rank() over(partition by dep_id order by salary desc) as salary_rnk from employee_ankitbansal) select dep_id, max(case when salary_rnk=1 then emp_name end) as highest_salaried_emps_in_dep, min(case when salary_rnk=2 then emp_name end) as lowest_salaried_emps_in_dep from highest_lowest_sal_cte group by dep_id; Thanks Ankit for the video 😊

[@akashgoel601]

Thanks for the video, sharing my approach.. used multiple CTE instead of case. with cte as ( select dep_id, MIN(salary) as min,MAX(salary) as max from employee_25Dec GROUP by dep_id), cte_max as ( SELECT b.dep_id,b.emp_name as emp_max--, b.emp_name as emp_min from cte a join employee_25Dec b on a.dep_id = b.dep_id where a.max = b.salary ), cte_min as ( SELECT b.dep_id,b.emp_name as emp_min--, b.emp_name as emp_min from cte a join employee_25Dec b on a.dep_id = b.dep_id where a.min = b.salary ) select a.dep_id, a.emp_max, b.emp_min from cte_max a join cte_min b on a.dep_id = b.dep_id

[@girdhar3224]

my sol: select dep_id, case when salary = (min(salary) over (partition by dep_id)) then emp_name end as min_sal_emp, case when salary = (max(salary) over (partition by dep_id)) then emp_name end as max_sal_emp from employee group by dep_id

[@vijiinfo]

select department_id, min(min_salary_name),max(max_salary_name) from ( select department_id, salary ,last_name , case when salary = (min(salary) over(partition by department_id ) ) then last_name else null end min_salary_name, case when salary = (max(salary) over(partition by department_id ) ) then last_name else null end max_salary_name from employees e where e.department_id in (30,90) group by department_id ,salary ,last_name ) group by department_id

[@ezequielsaldana8279]

Hi Ankit...Thanks for the content... I can resolved it from the following way: -------------------------------------------------------------------------- with cte as ( select dep_id, emp_name, salary, row_number() over(partition by dep_id order by salary) as order_salary from employees) select distinct dep_id, last_value(emp_name) over(partition by dep_id order by salary range between unbounded preceding and unbounded following) as emp_name_max_salary, first_value(emp_name) over(partition by dep_id order by salary) as emp_name_min_salary from cte; --------------------------------------------------------------------------------

[@ankitbansal6]

Thanks for posting 👏

[@avi8016]

Quite an interesting way to tackle the same problem, thankyou for the detailed process

[@ankitbansal6]

Glad you enjoyed it

[@vamsikrishna7927]

AT first I tried to solve by my self by looking only question with out watching entire video.....and i pulled out correctly thank you for your all sql videos which making me stronger day by day . here i my approach... WITH CTE AS (SELECT *, DENSE_RANK() OVER(PARTITION BY DEP_ID ORDER BY SALARY DESC) AS RANKK, DENSE_RANK() OVER(PARTITION BY DEP_ID ORDER BY SALARY ) AS RANKK2 FROM LOL_PRAC) SELECT DEP_ID, MAX(CASE WHEN RANKK=1 THEN EMP_NAME END) AS MAX_SAL, MAX(CASE WHEN RANKK2=1 THEN EMP_NAME END) AS MIN_SAL FROM CTE GROUP BY DEP_ID ;

[@ankitbansal6]

Excellent

[@akshayjoshi4106]

Keeping in mind salary amount same for multiple employees, my try in Oracle SQL: with cte as(select e.*,dense_rank() over (partition by dep_id order by salary asc) sal_low ,dense_rank() over (partition by dep_id order by salary desc) sal_high ,row_number() over (partition by dep_id order by salary asc) rn_low ,row_number() over (partition by dep_id order by salary desc) rn_high from employee e) select dep_id, max(case when sal_low = 1 and rn_low = 1 then emp_name end) low ,max(case when sal_high = 1 and rn_high = 1 then emp_name end) high from cte group by dep_id /

[@ashishroshan5946]

Akshay Joshi Why are you taking only one employee if multiple have the same salary. Won't it be better to use STR_AGG to have all the employee's name from a particular dep with max or min salary separated by commas in a single cell

[@zizu7755]

Hi Ankit, thanks for elaboration of all problems and knowledge you share with us on this channel. It means a lot. In this case, I definitely prefer second approach.

[@shekharkarade1067]

Hi brother, I used different approach and found same results. select dep_id1, min_salry , max_salry from (select dep_id as dep_id1, emp_name as min_salry from employee where salary IN (select min(salary) from employee group by dep_id) group by dep_id) as a left join (select dep_id, emp_name as max_salry from employee where salary IN (select max(salary) from employee group by dep_id) group by dep_id) as b on a.dep_id1 = b.dep_id Thank You for the video ❤

[@santhanabharathip7808]

Check this solution select distinct dep_id, FIRST_VALUE(emp_name) over(partition by dep_id order by salary desc) as emp_max_salary, FIRST_VALUE(emp_name) over(partition by dep_id order by salary) as emp_min_salary from employee

[@IswaryaMaran]

Your videos are all very interesting and well-explained. here's my try using rank and count: with emp_salary as (select *,rank() over (partition by dep_id order by salary desc) as rnk, count(1) over(partition by dep_id) as cont from employee) select dep_id, min(case when rnk=1 then emp_name else null end) as max_salary_emp, min(case when rnk=cont then emp_name else null end) as min_salary_emp from emp_salary group by dep_id

[@ankitbansal6]

Looks good. Thank you 😊

[@ritudahiya6223]

Can you please explain what is significance of using min Or max before case when

[@ajinkyamali1514]

Another approach is using Dense_rank() select * from ( select employee.* ,dense_rank() over (partition by dept_id order by desc/asc) as rank1 from employee) where rank1=1;

[@ankitchauhan6629]

Hello Ankit! Thank you posting such contents, Love it. Quick question - the second solution doesn't cover the scenario where there are multiple records in the same department having same highest or same lowest salaries. When we take Min or max of emp names, it prints only one emp name based on alphabetical order.

[@venkatesanvenki3721]

I'm following your SQL videos last 1 month, I learned alot. Thank you so much. I plan to learn python for data analysis. Can you please recommend me any youtube channel for same kind for analysis your doing on SQL.

[@sowmya6471]

Thank you for all your SQL videos. Its helping me a lot.

[@muhammedjasir2659]

with cte as ( select *, ROW_NUMBER()over(partition by dep_id order by salary desc) as rn from employee) select dep_id, max(case when rn =1 then emp_name else null end )as emp_with_min_sal ,max(case when rn =2 then emp_name else null end) as emp_with_max_sal from cte group by dep_id

[@KapilKumar-hk9xk]

wow, alway mind opening yet simple solutions.

[@mrinalbhardwaj2583]

Hey, following in my approach with cte as(select dep_id as 'dep',emp_name as 'name',salary as 'sal',RANK() over(partition by dep_id order by salary) rk from employee1), cte2 as(select dep as 'dep1',name as 'emp_name_max_salary' from cte where rk=(select max(rk) from cte)), cte3 as(select dep as 'dep2',name as 'emp_name_min_salary' from cte where rk=(select min(rk) from cte)) select cte.dep,cte2.emp_name_max_salary,cte3.emp_name_min_salary from cte,cte2,cte3 where cte.dep=cte2.dep1 and cte.dep=cte3.dep2 group by cte.dep,cte2.emp_name_max_salary,cte3.emp_name_min_salary

[@aaravkumarsingh4018]

with cte as( select dep_id,emp_name, rank() over(partition by dep_Id order by salary) as low_high, rank() over(partition by dep_Id order by salary desc) as high_low from employee ) select dep_id, max(case when high_low=1 then emp_name end) as emp_name_max_salary, max(case when low_high=1 then emp_name end) as emp_name_min_salary from cte group by dep_id;

[@sowmyakrishnam1444]

please make some videos on SQL server data tools scenario based. Just love your way of teaching

[@ankitbansal6]

Thank you. Sorry I didn't get what you mean by SQL server data tools ?

[@sowmyakrishnam1444]

@@ankitbansal6 i mean SSIS,SSRS,SSAS

[@tahakhalid324]

Hi Aknit Sir, My answer is with max_and_min_salaries as ( SELECT emp_name, dep_id, salary, dense_rank()over(partition by dep_id order by salary desc) max_salary, dense_rank()over(partition by dep_id order by salary) min_salary from employee ) , min_salaries_employess as ( SELECT dep_id,emp_name as min_salary_employee from max_and_min_salaries where min_salary = 1 ) , max_salaries_employess as ( SELECT dep_id,emp_name as max_salary_employee from max_and_min_salaries where max_salary = 1 ) SELECT max.dep_id, max.max_salary_employee, min.min_salary_employee from max_salaries_employess max join min_salaries_employess min on max.dep_id=min.dep_id

[@chandrarahul1990]

I tried using subqueries select dep_id, max(case when max_sal = 1 then emp_name end) as emp_name_max_salary, min(case when min_sal = 1 then emp_name end) as emp_name_min_salary from (select *, dense_rank() over (partition by dep_id order by salary desc) as max_sal, dense_rank() over (partition by dep_id order by salary asc) as min_sal from employee)a group by dep_id

[@dhrumilgohel1655]

Great video sir. I have one doubt like is subquery is not good in sql as it might increase the time complexity of query execution ? my first solution: with cte as (select dep_id,emp_name as max_sal from employee where (dep_id,salary) in (select dep_id,max(salary) from employee group by dep_id)), cte2 as (select dep_id,emp_name as min_sal from employee where (dep_id,salary) in (select dep_id,min(salary) from employee group by dep_id)) select c.dep_id,c.max_sal,cc.min_sal from cte c inner join cte2 cc on c.dep_id = cc.dep_id;

[@dfkgjdflkg]

This is great content. such an amount of informatiion.

[@khushishrivastava331]

Hello Ankit my approach to the problem is ;with cte as (select emp_name, max(salary) over(partition by dep_id) as max_sal, min(salary) over(partition by dep_id) as min_sal from employee ) select e.emp_name, e.salary from employee as e join cte as c on e.emp_name=c.emp_name and (e.salary=c.max_sal or e.salary=c.min_sal) order by e.dep_id let me know if its correct and we can use it Thanks!

[@anshulgupta1611]

Thanks, i liked your approach. but i tried with join, please correct if this approach is not optimized one. Select e.Dep_id,e.emp_name as MaxSalary, m.emp_name as MinSalary From ( (Select emp_name,Dep_id,salary From ( Select *, RANK() Over (partition by dep_id order BY salary Desc) as salaryRank From employee) X Where salaryRank =1) e INNER JOIN (Select emp_name,Dep_id,salary From ( Select *, RANK() Over (partition by dep_id order BY salary Desc) as salaryRank From employee) X Where salaryRank =2) m ON e.dep_id = m.dep_id);

[@pradiptomandal6574]

with cte as (select *,max(salary) over(partition by dep_id ) as maxi, min(salary) over(partition by dep_id ) as mini from salary) select dep_id, max(case when salary=maxi then emp_name else null end) as emp_name_max_sal, min(case when salary=mini then emp_name else null end) as emp_name_min_sal from cte group by dep_id

[@pardhasaradhichodey5359]

select distinct dep_id, first_value(emp_name) over (partition by dep_id order by salary desc) as emp_name_max_salary, last_value(emp_name) over (partition by dep_id order by salary desc rows between unbounded preceding and unbounded following) as emp_name_min_salary from employee

[@mohdtoufique3786]

Hi Ankit...Thanks for the content... My approach for the problem 1) WITH max_min_salary AS( SELECT *,max(salary)OVER(PARTITION BY dep_id)AS max_salary, min(salary)OVER(PARTITION BY dep_id)AS min_salary FROM employee) SELECT dep_id,max(CASE WHEN salary=max_salary THEN emp_name END) AS max_salary_employee, max(CASE WHEN salary=min_salary THEN emp_name END) AS min_salary_employee FROM max_min_salary GROUP BY dep_id 2) WITH max_min_salary AS( SELECT dep_id,max(salary)AS maximum_salary,min(salary) AS minimum_salary FROM employee GROUP BY dep_id) SELECT e.dep_id, max(CASE WHEN e.salary=maximum_salary THEN emp_name END) AS max_emp_name, min(CASE WHEN e.salary=minimum_salary THEN emp_name END) AS min_emp_name FROM employee e INNER JOIN max_min_salary m ON e.dep_id=m.dep_id GROUP BY e.dep_id;

[@prabhatgupta6415]

same way i did thnks

[@vaibhavtiwari8670]

I am not able to understand why you used join with cte as (select *,dense_rank()over(partition by dep_id order by salary desc) as rn from employee) select dep_id, max(case when rn=1 then emp_name end )as highest_salary, max(case when rn=2 then emp_name end )as lowest_salary from cte group by dep_id this is really easy and less complex for getting the same output

[@mridulmaurya8383]

with abc as ( select emp_name, dep_id,salary, max(salary) over(partition by dep_id order by salary desc) as maxi, min(salary) over(partition by dep_id order by salary asc) as mini from employee1 ) select dep_id, max(case when salary=maxi then emp_name end) as salary_maxi, max(case when salary=mini then emp_name end) as salary_mini from abc group by dep_id

[@prajjwaljaiswal3419]

I opted 2nd approach.

[@ankitbansal6]

👍

[@JyotiYadav-gq1fs]

Can we make it easy with this query ---- with cte as ( select *, RANK() over (partition by dep_id order by salary asc) as min_salary from employee) select * from cte where min_salary=1

[@parmoddhiman678]

for mysql with cte as (SELECT * , max(salary) over(partition by dep_id) as max_salary, min(salary) over(partition by dep_id) as min_salary FROM employee) select c.dep_id, max(case when salary=max_salary then emp_name end ) as max_emp, max( case when salary=min_salary then emp_name end ) as min_emp from cte c group by 1

[@rajasharma9886]

select distinct dep_id,FIRST_VALUE(emp_name) over (partition by dep_id order by salary desc) maxx, FIRST_VALUE(emp_name) over (partition by dep_id order by salary asc) minn from employee

[@shankarpesala921]

with cte as ( select *,ROW_NUMBER() over (partition by dep_id order by salary asc) r1, ROW_NUMBER() over (partition by dep_id order by salary desc) r2 from EMPLOYEen) select * from (select dep_id d,emp_name mins from cte where r1=1) a join (select dep_id d,emp_name maxs from cte where r2=1) b on a.d=b.d

[@VISHALSINGH-jw2nn]

select dep_id,max(case when r1=1 then emp_name end) as emp_max_sal, max(case when r=1 then emp_name end) as emp_min_sal from (select *,row_number() over(partition by dep_id order by salary) as r, row_number() over(partition by dep_id order by salary desc) as r1 from employee )t group by dep_id

[@ansumansahoo8628]

Thank You So Much ! It will Help us a lot 👍👍

[@akshayjain7937]

with cte as ( select *,ROW_NUMBER() over (partition by dep_id order by salary) as rn, first_value(emp_name) over (partition by dep_id order by salary) as min_salary_name, first_value(emp_name) over (partition by dep_id order by salary desc) as max_salary_name from employee_new ) select dep_id,min_salary_name,max_salary_name from cte where rn=1

[@prudhvithotapalli6596]

Hello Ankit for the two methods you wrote the solution what would be the case when there are two employees with same highest/lowest salary. What changes you would make to display both of their names

[@roshangangurde7188]

Will use DENSE RANK () function instead of ROW_ NUMBER function. As when ROW_ NUMBER function detects two identical values it assigns different row number to records But DENSE RANK() assigns the same rank to all identical values and it also doesn't skip rank like RANK().

[@pr-rb6jl]

Thanks Ankit ...Superb

[@mohammedshoaib1769]

with cte as (select * ,MAX(salary) over (partition by dep_id) as max_sal ,MIN(salary) over (partition by dep_id) as min_sal from employee) select cte.dep_id, max(case when salary=max_sal then emp_name end) as max_sal_emp ,max(case when salary=min_sal then emp_name end) as min_sal_emp from cte group by cte.dep_id

[@shravyasuvarna1229]

with class as ( select dep_id,salary,emp_name,row_number() over (partition by dep_id order by salary desc) as min_emp,row_number() over (partition by dep_id order by salary asc) as max_emp from employee_class) select dep_id,max(case when max_emp=2 then emp_name else null end) as max_emp_name, max(case when min_emp=2 then emp_name else null end )as min_emp_name from class group by dep_id

[@akshitdadheech9870]

Will this question be asked in for a data analyst fresher going for an interview? Also can you make a separate playlist for interview questions for Data Analyst Fresher according to you thanks 😅

[@ankushjain4128]

I like your way of explanation ..can you guide to read execution plan which approach is more optimized like same have 2 approach ?

[@ankitbansal6]

Yes, sure

[@shalubharvat1705]

How can we get 1, last and 3rd purchase date of every customers??

[@shushantkumar8396]

this is my solution for this problem- with cte as( select dep_id,emp_name from( select dep_id,emp_name,row_number() over(partition by dep_id order by salary desc) rn from employee ) tbl where rn=1), dte as (select dep_id,emp_name from( select dep_id,emp_name,row_number() over(partition by dep_id order by salary asc) rn from employee ) tb where rn=1) select c.dep_id,c.emp_name as highest_salary_employee,d.emp_name as lowest_salary_employee from cte c join dte d on c.dep_id=d.dep_id

[@rahulmehla2014]

my approach: with cte as (select *, dense_rank() over(partition by dep_id order by salary desc) as h_rn, dense_rank() over(partition by dep_id order by salary ) as l_rn from employee) select dep_id, max(case when h_rn = 1 then emp_name end) as emp_name_max_salary, max(case when l_rn = 1 then emp_name end) as emp_name_min_salary from cte group by dep_id

[@AmanDancetunesaman]

with cte as (select emp_name,salary,dep_id, dense_rank() over(partition by dep_id order by salary) as rnk from employee), cte2 as ( select dep_id,case when rnk in (select min(rnk) from cte) then emp_name end as min_salary_employee_name, case when rnk in (select max(rnk) from cte) then emp_name end as max_salary_employee_name from cte) select dep_id,max(max_salary_employee_name)as max_salary_employee_name ,max(min_salary_employee_name)as min_salary_employee_name from cte2 group by dep_id

[@gobindabehera5918]

select dep_id, max(case when salary=salary then emp_name end) max_salary, min(case when salary=salary then emp_name end) min_salary from employee_7 group by dep_id

[@vaishaliambasta1045]

I tried using 2 cte and it is done : with max as (select dep_id, emp_name, salary from employee where salary in (select max(salary) from employee group by dep_id)), min as(select dep_id, emp_name, salary from employee where salary in (select min(salary) from employee group by dep_id)) select m.dep_id, m.emp_name as emp_name_max_salary ,n.emp_name as emp_name_min_salary from max m join min n on m.dep_id=n.dep_id;

[@tanyamoghe8928]

SELECT DISTINCT dep_id, first_value(emp_name) OVER (partition By dep_id ORDER BY salary DESC ) as max_salary_emp, last_value(emp_name) OVER (partition By dep_id ORDER BY salary DESC Range between unbounded preceding and unbounded following ) as min_salary_emp FROM employee;

[@prabhatgupta6415]

Too good.. SELECT distinct dep_id, first_value(emp_name) OVER (partition By dep_id ORDER BY salary DESC ) as max_salary_emp, first_value(emp_name) OVER (partition By dep_id ORDER BY salary ASC) as min_salary_emp from employee5

[@Alexpudow]

These won't work if you have two or more equal max or min salaries in one departmint🙂

[@nightoutwithnifty4734]

with cte as ( select emp_name,dep_id,salary,max(salary)over (partition by dep_id) as max_salary, min(salary)over (partition by dep_id) as min_salary from employee) select dep_id, max(case when salary = max_salary then emp_name else null end) as max_sal_emp, max(case when salary = min_salary then emp_name else null end) as min_sal_emp from cte group by 1

[@mohammedriyaz-g4s]

with cte as (select case when max_salary =1 then emp_name end max_sal_emp, case when min_salary =1 then emp_name end min_sal_emp,dep_id from (select * , dense_rank() over (partition by dep_id order by salary desc) max_salary, dense_rank() over (partition by dep_id order by salary asc) min_salary from employee)) select dep_id,max(max_sal_emp) as max_sal_emp,max(min_sal_emp)as min_sal_emp from cte group by dep_id

[@radhikagupta7314]

select emp_name,salary,dep_id, case when rank1=1 and dep_id=1 then 'Lowest in Department 1' when rank1=1 and dep_id=2 then 'Lowest in Department 2' when rank1=count_emp and dep_id=1 then 'HIghest in Department 1' when rank1=count_emp and dep_id=2 then 'Highest in Department 2' end as "comment" from ( select e.*,rank() over (partition by dep_id order by salary asc) as rank1, count(1) over (partition by dep_id ) as count_emp from employee e ) where rank1=1 or rank1=count_emp

[@Priyanka-us8rw]

Is that possible to have doc as well for every interview questions you are sharing in you videos

[@DurgaKeshari]

My solution is.... 1) select dep_id,a.emp_name as emp_name_max_salary ,b.emp_name as emp_name_min_salary from (select dep_id,emp_name from employee where salary in (select max(salary) from employee group by dep_id)) a join (select dep_id,emp_name from employee where salary in (select min(salary) from employee group by dep_id)) b using(dep_id) order by 1

[@rajn2155]

select dep_id,max(case when dr=1 then emp_name end) as Emp_with_MAX_sal, max(case when ar=1 then emp_name end )as Emp_with_MIN_sal from( select *,dense_rank() over(partition by dep_id order by salary desc) as dr ,dense_rank() over(partition by dep_id order by salary) as ar from employee ) aa group by dep_id

[@Sambhavjain1301]

with high_salary as (select *, Rank() over(partition by dep_id order by salary desc) as rnk from emp), low_salary as (select *, Rank() over(partition by dep_id order by salary asc) as rnk from emp) select h.dep_id, h.emp_name, l.emp_name from high_salary as h inner join low_salary as l on h.dep_id=l.dep_id where h.rnk=1 and l.rnk=1

[@anishchhabra6085]

my solution in MySQL (I solved without looking into the above solution): with sal_rank as ( select *, rank() over(partition by dep_id order by salary desc) as rnk from emp) select dep_id, max(case when rnk = 1 then emp_name end) as 'emp_name_max_salary', max(case when rnk = (select count(*) from emp group by dep_id order by count(*) desc limit 1) then emp_name end) as 'emp_name_min_salary' from sal_rank group by dep_id; Can anyone inform me if the solution is correct or not, I am getting the required answer for this scenario but I wanted to ask for any edge case where this might fail.

[@NikitaMate-i3f]

select dep_id, max(case when low_rk=1 then emp_name end ) as low_salary_emp, max(case when high_rk=1 then emp_name end )as high_salary_emp from( select *,rank() over(partition by dep_id order by salary ) as low_rk, rank() over(partition by dep_id order by salary desc) as high_rk from employee) a group by dep_id ;

[@saiswaroop3570]

select dep_id,min(case when salary=min_sal then emp_name end) as min_sal,min(case when salary=max_sal then emp_name end) as max_sal from ( select emp_name,dep_id,salary,min(salary)over(partition by dep_id order by salary range between unbounded preceding and unbounded following) min_sal , max(salary)over(partition by dep_id order by salary range between unbounded preceding and unbounded following) max_sal from employee ) group by dep_id

[@sushilkumarpatil2539]

with cte as (select distinct dep_id, (case when salary=max(salary) over(partition by dep_id) then emp_name end) as dep_max_salary, (case when salary=min(salary) over(partition by dep_id) then emp_name end) as dep_min_salary from employee) select dep_id,max(dep_max_salary) as dep_max_salary,min(dep_min_salary) as dep_min_salary from cte group by dep_id;

[@arshadmohammed2346]

Can you explain in detail about min and max that are used along with case when?

[@inspiredomkar1239]

What if there are multiple employees with the highest salary? I tried using duplicate data where the first highest and first lowest salary was same but names were different ,It gave me the record which comes first alphabetically.

[@Pavas-n6j]

with cte1 as (select dep_id,emp_name, rank() over (partition by dep_id order by salary desc) as rnk from employee), cte2 as (select dep_id,emp_name, rank() over (partition by dep_id order by salary) as rnk from employee) select * from cte1 inner join cte2 on cte1.dep_id=cte2.dep_id where cte1.rnk=1 and cte2.rnk=1;

[@ramakumarguntamadugu1299]

great content as always 👌👍 can you please make a video on SQL functions and stored procedures if possible

[@ankitbansal6]

Great suggestion!

[@arupchandra8602]

select a.dep_id,MAX(a.emp_name_max_sal) as emp_name_max_sal,MIN(a.emp_name_min_sal) as emp_name_min_sal from( select *, case when salary=max(salary) over(partition by dep_id) then emp_name end emp_name_max_sal, case when salary=min(salary) over(partition by dep_id) then emp_name end emp_name_min_sal from employee)a group by a.dep_id

[@ritudahiya6223]

@ankit bansal Can you please explain what is significance of using min Or max before case when

[@ankitbansal6]

Watch this kzbin.info/www/bejne/hWfQdZeLfturb80

[@Arnob_111]

What if I want to display the individual salary of max_sal_emp and min_sal_emp alongside?

[@Ankitatewary-q6w]

with cte as( select *, max(salary) over(partition by dep_id) max_sal, min(salary) over (partition by dep_id) min_sal from employee), max_table as( select dep_id,emp_name as max_sal_emp from cte where salary=max_sal), min_table as( select dep_id,emp_name as min_sal_emp from cte where salary=min_sal) select max_table.*,min_table.min_sal_emp from max_table join min_table on (max_table.dep_id=min_table.dep_id);

[@kailashpatro5768]

select dep_id ,max(case when highest = 1 then emp_name end )as emp_name_max_salary, max( case when highest 1 then emp_name end )as emp_name_min_salary from ( select *,rank() over(partition by dep_id order by salary desc) as highest from employee) a group by dep_id

[@Winter_Soldier100]

with xyz as (Select * , row_number() over(partition by dep_id order by salary desc ) as rn FROM employee) Select dep_id , MAX(case when rn=1 then emp_name end )as emp_high_salary, MAX(case when rn=2 then emp_name end )as emp_low_salary from xyz group by dep_id Is this approach correct ?

[@TargaryenGaming-u3u]

no what if you have more than 2 rows then it will give wrong data for low_salary emp

[@meghnasoni]

My solution: with cte as(select *, max(salary) over( partition by dep_id order by salary desc) as mx, min(salary) over( partition by dep_id order by salary ) as mn from employee) SELECT dep_id, max(case when salary = mx then emp_name end )as max_sal_emp, max(Case when salary = mn then emp_name end )as min_sal_emp FROM cte GROUP by 1

[@swamivivekananda-cyclonicm8781]

Thank you for your efforts

[@ankitbansal6]

My pleasure

[@aayushibirla2590]

very nice solution

[@ankitbansal6]

Thank you! Cheers!

[@NamanSeth1]

I tried combination of both the approaches on my own @AnkitBansal :D Thanks for the great videos. Keep educating us. My Solution: with cte as( select *,dense_rank() over (partition by dep_id order by salary desc) rank, min(salary) over (partition by dep_id) min_salary from employee ) select dep_id, max(case when rank=1 then emp_name end) as max_sal_emp , max(case when salary=min_salary then emp_name end) as min_sal_emp from cte group by dep_id

[@ankitbansal6]

Nice work!

[@MohitKumar-jp5wy]

Both scenarios will fail if more than 1 employee gets (min/max) same salary, as more than 1 employee can get the department's min/max salary.

[@snowflaketrainingintelugu]

Just change the row number to rank if more employees has max or min sal

[@parmoddhiman678]

by window fxn with cte as (SELECT * , max(salary) over(partition by dep_id) as max_salary, min(salary) over(partition by dep_id) as min_salary FROM employee) select c.dep_id, max(case when salary=max_salary then emp_name end ) as max_emp, max( case when salary=min_salary then emp_name end ) as min_emp from cte c group by 1

[@kanikeveeresh4448]

select A.dep_id, A.emp_name as higher_Emp_salary,B.emp_name as Lowest_salary_emp from ( select dep_id,emp_name,salary from ( select *,row_number() over(partition by dep_id order by salary desc) as [rank] from employee_1 ) A where [rank]=1 ) as A inner join (select dep_id,emp_name,salary from ( select *,row_number() over(partition by dep_id order by salary asc) as [rank] from employee_1 ) A where [rank]=1) as B on A.dep_id=B.dep_id

[@Progressivethoughts]

Thankyou so much Ankit and there was one more table named department so instead of department id can we get department name from another table in same output

[@ankitbansal6]

Yes you need to just join with dep table and get dep name and select name instead of id

[@Progressivethoughts]

@@ankitbansal6 i'll try

[@sannigupta4042]

select b.emp_name as emp_name_max_salary ,d.emp_name as emp_name_min_salary,b.dep_id from (select a.* from (select *, row_number() over (partition by dep_id order by salary desc) as rn from Employee ) a where a.rn = 1) b join (select c.* from (select *, row_number() over (partition by dep_id order by salary desc) as rn from Employee ) c where c.rn = 2) d on b.dep_id = d.dep_id

[@zing_king]

But it will not give correct result if there will be same salary records in the same department

[@p51_ishansingh49]

Using Join and Window Function select t1.dep_id,t1.emp_name_max_salary, t2.emp_name_min_salary from (select dep_id,emp_name as emp_name_max_salary from (select *, dense_rank () over(partition by dep_id order by salary desc) as rnk from employee) tab1 where rnk=1) t1 join (select dep_id, emp_name as emp_name_min_salary from (select *, dense_rank () over(partition by dep_id order by salary) as rnk from employee) tab1 where rnk=1) t2 on t1.dep_id=t2.dep_id;

[@vru5696]

I did asc and desc rank wise in 2 different cte's and doing inner join based on dept id and at last where rn1=1 and rn2=2

[@ankitbansal6]

Can you write the query

[@vru5696]

@@ankitbansal6 with cte as (select *, row_number() over (partition by depid order by sal desc) rn1 from emp), cte2 as (select *, row_number() over (partition by depid order by sal desc)rn2 from emp) select cte.depid,cte.ename as ename_max_salary, cte2.ename as ename_min_salary from cte2 inner join cte on cte2.depid=cte.depid where rn=1 and rn2=2

[@animeshchittora5693]

@@vru5696 Even I thought the same

[@anudeepgupa]

My way: select distinct dep_id, first_value(emp_name) over(partition by dep_id order by salary desc) as max_sal_emp, First_value(emp_name) over(partition by dep_id order by salary asc) as min_sal_emp From employee Note: ignore spelling errors as I am commenting from mobile

[@asif_sahara6326]

SELECT dep_id, max(case when salary == max then emp_name end) as max_salary, max(case when salary == min then emp_name end) as min_salary FROM (SELECT dep_id, emp_name, salary, max(salary) over(partition by dep_id) as max, min(salary) over(partition by dep_id) as min from employee) as temp group by dep_id; ans-> dep_id max_salary min_salary 1 Prasad Siva 2 Ravi Sai

[@mathavansg9227]

with cte as (SELECT *,max(salary) over(partition by dep_id) as max_salary, min(salary) over(partition by dep_id) as min_salary from employee) SELECT c.emp_name,c.dep_id,c.salary,e.emp_name as highest_paid_employee,e1.emp_name as Low_paid_employee from cte as c inner join employee as e on c.max_salary=e.salary inner join employee e1 on c.min_salary=e1.salary

[@VInuthnaGottapu]

Namaste sir How can we get both min and max values in same column for each department ?

[@ankitbansal6]

You need to create 2 columns

[@VInuthnaGottapu]

@@ankitbansal6 thankyou sir will try to do it

[@rallabandivijayvijay5456]

; with cte_x as ( select dept,max(salary) as max_salary,min(salary) as min_salary from salaries group by dept ) select b.dept,b.emp_name as max_sal_emp_name,c.emp_name as min_sal_emp_name from cte_x a join salaries b on a.dept=b.dept and b.salary=a.max_salary join salaries c on c.salary=a.min_salary and a.dept=c.dept

[@Mysingh9767]

Hi Ankit This is correct or not....WITH Highest_Salary AS (SELECT dep_id,CASE WHEN Salary = MAX(Salary) THEN emp_name END AS Highest FROM employee GROUP BY 1), Lowest_Salary AS (SELECT dep_id,CASE WHEN Salary = MIN(Salary) THEN emp_name END AS Lowest FROM employee GROUP BY 1) SELECT H.dep_id,H.Highest,L.lowest FROM Highest_Salary H INNER JOIN Lowest_Salary L ON H.dep_id=L.dep_id GROUP BY 1

[@ankitbansal6]

Try running it ..it may produce error

[@akashkonda8385]

with cte1 AS ( select dep_id, emp_name as emp_max_salary from employee where salary in (select max(salary) from employee group by dep_id) ), cte2 AS (select dep_id, emp_name as emp_min_salary from employee where salary in (select min(salary) from employee group by dep_id) ) select a.*, b.emp_min_salary from cte1 a join cte2 b on a.dep_id = b.dep_id order by a.dep_id ;

[@guptaaniket4155]

SELECT DISTINCT(dep_id), FIRST_VALUE(emp_name) OVER(PARTITION BY dep_id ORDER By salary RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS MIN_SALARY, LAST_VALUE(emp_name) OVER(PARTITION BY dep_id ORDER By salary RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS MAX_SALARY FROM employee

[@harishkumar4663]

This solution would not work if we have multiple employees with Highest and lowest salary

[@hairavyadav6579]

Nice