.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
abhays
Clintonzz
arronlee
mattyclown
cathyhill345

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

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
 Subscribe to Articles

     

Further Readings:

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

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend