WIPRO SQL Interview Question - FIRST_VALUE( ) Function

[@Harish0402]

-----1 approach using first_value analytical function select distinct dept_id , first_value(emp_name) over(partition by dept_id order by salary asc) min_sal_emp first_value(emp_name) over(partition by dept_id order by salary desc) max_sal_sal from emps_tbl group by dept_id,min_Sal_emp,max_sal_emp; ---2 Approach using row_number analytical function with cte as (select e.* , row_number() over(partition by dept_id order by salary asc ) rn_min_sal , row_number () over(partition by dept_id order by salary desc) rn_max_sal from emps_tbl e) select dept_id , max(case when rn_min_sal =1 then emp_name end) as min_sal_emp, max(case when rn_max_sal =1 then emp_name end ) as max_sal_emp from cte group by dept_id;

[@dasubabuch1596]

with t as ( select dept_id, first_value(emp_name)over(partition by dept_id order by salary) as min_sal_empname, last_value(emp_name)over(partition by dept_id order by salary rows between unbounded preceding and unbounded following) as max_sal_empname from emps_wipro ) select distinct dept_id, min_sal_empname,max_sal_empname from t;

[@sravankumar1767]

Nice explanation bro 👍 👌 👏

[@CloudChallengers]

@sravankumar1767, Thanks for the encouragement.

[@Hope-xb5jv]

with cte as ( select *,DENSE_RANK()over(partition by dept_id order by salary desc) as maxsal, DENSE_RANK()over(partition by dept_id order by salary ) as minsal from emps_min_max ) select c.dept_id,c1.emp_name as min_sal_emp ,c.emp_name as max_sal_emp from (select * from cte where maxsal = 1) c join (select * from cte where minsal = 1) c1 on c.dept_id = c1.dept_id

[@CloudChallengers]

@Hope-xb5jv, Thanks for sharing different approach.

[@kingvirat9912]

We can use group by clause in first question. this is very easy and simple to understand

[@CloudChallengers]

@kingvirat9912, l would be appreciated if you can share your query here.

[@kingvirat9912]

@@CloudChallengers SELECT dept_id, MIN(salary) AS min_salary, MAX(salary) AS max_salary FROM salary_table GROUP BY department_id;

[@AbdAbd-cl6nt]

We can’t use group by here caz it give outputs only for aggregate columns not for name column

[@shahzan525]

Good video❤

[@CloudChallengers]

@shahzan525, Thanks for the feedback

[@hairavyadav6579]

with cte as ( select *, first_value(salary) over(partition by dept_id order by salary) min , first_value(salary) over(partition by dept_id order by salary desc) max from emps) select dept_id,max(case when salary = min then emp_name end) as min_salary_emp_name, max(case when salary = max then emp_name end) as max_salary_emp_name from cte group by 1;

[@sunilsahoo5199]

with sal_cte as(select min(salary) as min_sal,max(salary)as max_sal,dept_id from emps_tbl group by dept_id) select a.dept_id, max(case when b.salary=a.min_sal then b.emp_name end) as min_sal_empname, max(case when b.salary=a.max_sal then b.emp_name end) as max_sal_empname from sal_cte a inner join emps_tbl b on a.dept_id=b.dept_id group by a.dept_id

[@monasanthosh9208]

Select distinct(Dept_id),first_value(Emp_Name) over (Partition by dept_id order by salary desc) as Max_sal_Emp, first_value(Emp_Name) over (Partition by dept_id order by Salary asc) as Min_Sal_Emp from emps_TBL ;

[@venkateshdhanasekaran5398]

with cte as ( select *,first_value(salary) over(partition by dept_id order by salary rows between unbounded preceding and unbounded following) as min_salary, last_value(salary) over(partition by dept_id order by salary rows between unbounded preceding and unbounded following) as max_salary from emps_tbl) select c.dept_id, max(case when c.salary = min_salary then c.emp_name end) as Min_Salary_EmpName, max(case when c.salary = max_salary then c.emp_name end) as Max_Salary_EmpName from cte c group by c.dept_id;

[@anirbanbiswas7624]

with cte as (select *,rank() over(partition by dept_id order by salary desc) as rnk from emps_tbl) select dept_id, max(case when rnk =1 then emp_name end) as max_sal_emp, min(case when rnk >1 then emp_name end) as max_sal_emp from cte where rnk

[@Max_Gamer_21]

with low as (select dept_id,emp_name as min_sal_emp,row_number() over(partition by dept_id order by salary asc) as rk,row_number() over(partition by dept_id order by salary asc) as r from emps_tbl ), high as (select dept_id,emp_name as max_sal_emp,row_number() over(partition by dept_id order by salary desc) as dk,row_number() over(partition by dept_id order by salary desc) as rr from emps_tbl ) select l.dept_id,l.min_sal_emp,h.max_sal_emp from low l join high h on l.dept_id=h.dept_id where (rk=1 or r=1) and (dk=1 or rr=1)

[@thrupthilc3060]

Not able to use first_value function in mysql

[@CloudChallengers]

@thrupthilc3060, first_value function should work in mysql. Check your query once

[@anirbanbiswas7624]

0:24 may u share the error i mail?it should work in mysql

[@akash.i7391]

Bro.. I am using oracle database... Is there any change in oracle ?

[@CloudChallengers]

@akash.i7391, It should work in oracle as well.

[@akash.i7391]

@@CloudChallengers but in Oracle first values function includes some statement like unbounded preceding something) ..if i was not using this key word ,it through error..I am using Oracle 11 g version

[@KumarHemjeet]

----2nd approach select x1.dept_id, x1.max_sal_emp, x2.min_sal_emp from ( select e.dept_id, e.emp_name as max_sal_emp from employees e where e.salary = ( select max(salary) from employees e2 where e2.dept_id = e.dept_id ) ) x1 left join ( select e.dept_id, e.emp_name as min_sal_emp from employees e where e.salary = ( select min(salary) from employees e2 where e2.dept_id = e.dept_id ) ) x2 on x1.dept_id = x2.dept_id;

[@vikassolanki297]

With cte as( select *, row_number() over(partition by dept_id order by salary) as rn ,row_number() over(partition by dept_id order by salary desc) as rn1 from emps_tbl) select dept_id, max(case when rn = 1 then emp_name end) as min_sal_empname, max(case when rn1 = 1 then emp_name end) as max_sal_empname from cte group by dept_id

[@chandanpatra1053]

please bring some medium level sql questions which require brain storming

[@MubarakAli-qs9qq]

Is this a easy one ??

[@chandanpatra1053]

@@MubarakAli-qs9qq I think you starting your sql journey.

[@MubarakAli-qs9qq]

@@chandanpatra1053 yes its been a week , u ??

[@hairavyadav6579]

my solution please let me know this is fine or not with cte as( select *, dense_rank() over(partition by dept_id order by salary) min ,dense_rank() over(partition by dept_id order by salary desc) max from emps) select dept_id,max(case when min = 1 then emp_name end) as min_salary, max(case when max=1 then emp_name end) as max_salary from cte group by 1;

[@arjundev4908]

WITH CTE AS(SELECT *, row_NUMBER()OVER(partition by dept_id ORDER BY SALARY) AS RW FROM SALARY),V1 AS( SELECT *, CASE WHEN RW = min(RW) OVER(partition by dept_id) THEN 1 WHEN RW = max(RW) OVER(partition by dept_id) THEN 0 END AS QUALIFY FROM CTE) SELECT dept_id, MIN(CASE WHEN QUALIFY = 1 THEN emp_name END) AS MIN_SAL_EMP, MIN(CASE WHEN QUALIFY = 0 THEN emp_name END) AS MAX_SAL_EMP FROM V1 group by 1;

[@lakshmanlee3579]

SQL code for this question: select distinct dept_id,first_value(emp_name) over (partition by dept_id order by salary) min_salary, first_value(emp_name) over (partition by dept_id order by salary desc) max_salary from emps_tbl

[@MovieBuzz-uu8kp]

select dept_id, max(case when salary = min_salary then emp_name end) as min_salary, max(case when salary = max_salary then emp_name end) as max_salary from ( select *, min(salary) over (partition by dept_id) as min_salary, max(salary) over (partition by dept_id) as max_salary from emps_tbl ) a group by dept_id

[@theinsightminer08]

WITH CTE AS (SELECT *, FIRST_VALUE(emp_name) OVER(PARTITION BY dept_id ORDER BY salary rows between unbounded preceding and unbounded following) as min_sal_empname, LAST_VALUE(emp_name) OVER(PARTITION BY dept_id ORDER BY salary rows between unbounded preceding and unbounded following) as max_sal_empname FROM emps_tbl) SELECT DISTINCT dept_id, min_sal_empname, max_sal_empname FROM CTE;

[@krishanukundu4565]

my approach- with dept_sal as ( SELECT *, max(salary) over(partition by dept_id) as max_dept_sal, min(salary) over(partition by dept_id) as min_dept_sal FROM salaries ) select dept_id, max(case when salary= max_dept_sal then name end) as max_sal_empname, max(case when salary= min_dept_sal then name end) as min_sal_empname from dept_sal group by 1

[@rohithr9122]

with cte as( select dept_id,MIN(salary)minsal,MAX(salary)maxsal from emps_tbl group by dept_id), cte2 as( select cte.dept_id,cte.minsal,cte.maxsal,e1.emp_name from cte join emps_tbl as e1 on cte.dept_id = e1.dept_id and cte.minsal = e1.salary) select cte2.dept_id,cte2.emp_name as min_sal_empname,e3.emp_name as max_sal_empname from cte2 join emps_tbl as e3 on cte2.maxsal = e3.salary and cte2.dept_id = e3.dept_id

[@Happyface-dm5nh]

for oracle SQL -- SELECT DEPT_ID, MIN(EMP_NAME) KEEP (DENSE_RANK FIRST ORDER BY SALARY) AS MINSALARYEMP, MAX(EMP_NAME) KEEP (DENSE_RANK LAST ORDER BY SALARY) AS MAXSALARYEMP FROM emps_tbl GROUP BY DEPT_ID;

[@shashank_1180]

with mini as( select dept_id , emp_name , ROW_NUMBER() over(partition by dept_id order by salary asc) minn from #emps_tbl) ,maxi as( select dept_id , emp_name , ROW_NUMBER() over(partition by dept_id order by salary desc) maxx from #emps_tbl) select a.dept_id,a.emp_name minsal_emp_name,b.emp_name maxsal_emp_name from mini a join maxi b on a.dept_id=b.dept_id and a.minn=1 and b.maxx=1

[@entertainmenthub5066]

with cte as( select dept_id,min(salary) as min,max(salary) as max from emps_tbl group by dept_id) select a.dept_id,max(emp_name) as min_name,min(emp_name) as max_name from emps_tbl a inner join cte b on a.dept_id=b.dept_id group by a.dept_id

[@himanshushorts7143]

with cte as ( select *,lag(emp_name) over(partition by a.dept_id order by salary desc) nxt from himanshudb.emps_tbl a join (select dept_id as dpt_id,min(salary) min_sal,max(salary) max_sal from himanshudb.emps_tbl group by dept_id) b on a.dept_id = b.dpt_id and a.salary = b.min_sal or a.salary = b.max_sal ) select dept_id,emp_name as min_sal_emp_name, coalesce(nxt,0) as max_sal_emp_name from cte where nxt != '0';

[@HARSHRAJ-gp6ve]

with cte as( select dept_id,MAX(salary) as max1 FROM emps_tbl GROUP BY dept_id ),cte1 as( select cte.dept_id,emp_name as max_man FROM cte JOIN emps_tbl ON cte.dept_id=emps_tbl.dept_id and cte. max1=emps_tbl.salary ), cte3 as( select dept_id,min(salary) as min1 FROM emps_tbl GROUP BY dept_id ),cte4 as( select cte3.dept_id,emp_name as min_man FROM cte3 JOIN emps_tbl ON cte3.dept_id=emps_tbl.dept_id and cte3. min1=emps_tbl.salary ) select cte1.dept_id,min_man,max_man FROM cte1 JOIN cte4 ON cte1.dept_id=cte4.dept_id;

[@samyukthashanmugam7516]

SELECT dept_id, (SELECT emp_name FROM emps_tbl WHERE dept_id = e.dept_id ORDER BY salary ASC LIMIT 1) AS min_sal_emp, (SELECT emp_name FROM emps_tbl WHERE dept_id = e.dept_id ORDER BY salary DESC LIMIT 1) AS max_sal_emp FROM emps_tbl e GROUP BY dept_id ORDER BY dept_id ASC;

[@vigneshkumara4682]

WITH CTE AS ( SELECT dept_id,min(salary) AS min_sal,max(salary) AS max_sal FROM employee GROUP BY DEPT_ID ) ,CTE1 AS (SELECT DEPT_ID,EMP_NAME AS max_empname FROM EMPLOYEE WHERE SALARY IN(SELECT MAX_SAL FROM CTE)) ,TAB1 AS(SELECT DEPT_ID,EMP_NAME AS min_empname FROM EMPLOYEE WHERE SALARY IN(SELECT MIN_SAL FROM CTE)) ,TAB2 AS(SELECT DEPT_ID,EMP_NAME AS max_empname FROM EMPLOYEE WHERE SALARY IN(SELECT MAX_SAL FROM CTE)) SELECT TAB1.DEPT_ID,TAB1.min_empname,TAB2.max_empname FROM TAB1 INNER JOIN TAB2 ON TAB1.DEPT_ID=TAB2.DEPT_ID ORDER BY DEPT_ID