WIPRO SQL Interview Question - FIRST_VALUE( ) Function

One of the SQL questions recently asked in WIPRO interview.
We need to Find department wise minimum salary empname and maximum salary empname .
To solve this, we will write a query by using FIRST_VALUE() windows function for first approach and Rownumber() function for second approach.
Create table syntax
CREATE TABLE emps_tbl (emp_name VARCHAR(50), dept_id INT, salary INT);
INSERT INTO emps_tbl VALUES ('Siva', 1, 30000), ('Ravi', 2, 40000), ('Prasad', 1, 50000), ('Sai', 2, 20000), ('Anna', 2, 10000);
For more SQL interview questions. Check out our playlist.
• SQL Interview Questions
Contact us:
[e-mail]
Follow us on
Instagram : cloudchallengers
Facebook : cloudchallengers
LinkedIn : linkedin.com/company/cloudchallengers

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

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

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

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

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

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

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

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

[@sravankumar1767]

Nice explanation bro 👍 👌 👏

[@CloudChallengers]

@sravankumar1767, Thanks for the encouragement.

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

[@shahzan525]

Good video❤

[@CloudChallengers]

@shahzan525, Thanks for the feedback

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

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

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

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