SQL Intermediate Level Question 3 - Find 3rd highest salary with a twist.

Question - Find 3rd highest salary of each dept if there are less than 3 employee then return salary of lowest employee.
Table Create Query :-
create table employees(
emp_id int primary key,
emp_name varchar(20),
emp_salary int,
dep_id int,
)
insert into employees
values(1,'Raj',15000,100),
(2,'Rohan',25000,100),
(3,'Ankit',25690,100),
(4,'Puja',20000,100),
(5,'Arun',58000,200),
(6,'Tarun',65000,200),
(7,'Pintu',45800,200),
(8,'Ankur',25000,300),
(9,'Ajay',17000,300),
(10,'Amrin',37500,400);
#sql #sqldeveloper #dataanalysis #sqlserver

[@ArabindaMohapatra]

Awesome Video Keep posting these kind of video

[@dfkgjdflkg]

Thanks for the inspiring videos. a new approach to the problems.

[@abinashpatra8234]

Bro you are doing nice ,keep it up 😊👍

[@gouthamstar6558]

I got emp id--2, 6,9,10

[@chandanpatra1053]

Bhai esa method to ankit bansal ke videos me bhi nahin bataya.Keep it up. I really like your content in sql playlist. Datetime ke upar jitne bhi functions hain in sql server ek complete video banao.

[@Code-Con]

sure bhai datetime functions cover karunga

[@gouthamstar6558]

with cte as (
select *, count(dep_id) over(partition by dep_id ) as dep_count,
ROW_NUMBER() over(partition by dep_id order by emp_salary ) as rn from employees
)
select * from cte where dep_count >=3 and rn=3
union
select * from cte
where dep_count

[@anirbanbiswas7624]

with cte as (select emp_name,emp_salary,dep_id,count(*) over(partition by dep_id) as cnt,
rank() over(partition by dep_id order by emp_salary desc) as rnk from employees),
output_case as(select emp_name,emp_salary,dep_id,cnt,rnk,
case when cnt=4 then rnk=3 else null end as final,
case when cnt=3 then rnk=3 else null end as final2,
case when cnt=2 then rnk=2 else null end as final3,
case when cnt=1 then rnk=1 else null end as final4
from cte)
select emp_name,emp_salary,dep_id from output_case where final=1 or final2=1 or
final3=1 or final4=1
I KNOW this code is bit lengthy but still solves the purpose easily

[@gouthamstar6558]

this is a complex method, only works for this particular data, static methods are not good and useful for dynamic data updates

[@apurvasaraf5828]

select dep_id,min(emp_salary) from (select * from (select * ,rank() over(partition by dep_id order by emp_salary desc) r,
count(dep_id) over(partition by dep_id) as c
from employees)p
where r=3 or c

[@gouthamstar6558]

emp id, 4,7 are not 3rd highest salary