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
Gold 1400 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
[
B]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