List the highest salary from each department?

Syedshakeer
Posted by Syedshakeer under Sql Server category on | Views : 5370
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

Comments or Responses

Posted by: Seeteshh on: 4/28/2010 Level:Starter | Status: [Member]
Can you highlight on the CTE part? Why have you done this?

Login to post response