create table empname
(
name varchar(20),
salary int,
deptno int
)
name salary deptno
inthiyaaz 2000 10
khaja 8000 10
Afroz 2000 20
Heera 1000 30
Naseeb 1000 10
King 1000 20
Imroz 3000 30
Naseer 2000 10
Pankaj 1500 30
--------------------------------------
Q1) List the highest salaries from each department?
Ans )with CTE as(select row_number() over(partition by deptno order by salary desc) row,salary,deptno from empname)
select * from CTE where row=1
OutPut :-
row salary deptno
1 8000 10
1 2000 20
1 2000 30
Question) List the Top 2 highest salaries from each department?
with CTE as(select row_number() over(partition by deptno order by salary desc) row,salary,deptno from empname)
select * from CTE where row<3
OutPut :-
row salary deptno
1 8000 10
2 3000 10
1 2000 20
2 1000 20
1 2000 30
2 1500 30
--------------------------------------
Q3)List the name of all Employees who are taking 3rd highest salaries?
Ans)Method 1
select Name,salary from empname
where salary=(select min(salary) from empname
where salary in (select top 3 salary from empname order by salary desc))
OutPut)
name salary
inthiyaaz 2000
Afroz 2000
Imroz 2000
Method 2
with CTE as(select dense_rank() over( order by salary desc) row,salary,deptno from empname)
select * from CTE where row=3
OutPut)
name salary
inthiyaaz 2000
Afroz 2000
Imroz 2000
Thank you
Syed Shakeer Hussain
|
You must Sign In To post reply |