Welcome :Guest

Congratulations!!!

Top 5 Contributors of the Month
hdjk11
Shivakumaran
dfefde5
hoangnganvy1993

 Home >> Articles >> DataBase >> Post New Resource

# List the highest salary from each department?

Posted By:Syed Shakeer Hussain       Posted Date: March 14, 2010    Points: 25    Category: DataBase    URL: http://www.dotnetspark.com

Important quries in sqlserver to find the highest salary value.

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

Responses

No response found. Be the first to respond this post

Post Comment