Welcome :Guest

 Sign In Register
Win Surprise Gifts!!!
Congratulations!!!

 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

You must Sign In To post reply
 Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here