Interested to Buy Any Domain ? << Click Here >> for more details...
Find top Nth employee from each department in terms of
salary?
- 13 Answers
- 25621 Views
- RealPage, I also Faced
- E-Mail Answers
Answer Posted / gopi muluka
Execute below query against Adventureworks database
WITH CTE AS(
SELECT D.Name AS Department,E.EmployeeID,(RANK() OVER
(PARTITION BY D.Name ORDER BY MAX(Rate) DESC)) AS
EmployeeRank, MAX(Rate) AS HourlyRate
FROM [AdventureWorks].[HumanResources].[Employee] E
INNER JOIN HumanResources.EmployeePayHistory PH
ON E.[EmployeeID]=PH.[EmployeeID]
INNER JOIN HumanResources.EmployeeDepartmentHistory DH
ON E.EmployeeID=DH.EmployeeID
AND DH.EndDate IS NULL
INNER JOIN HumanResources.Department D
ON DH.DepartmentID=D.DepartmentID
GROUP BY D.Name,E.EmployeeID
)
SELECT * FROM CTE WHERE EmployeeRank=2
ORDER BY Department,EmployeeRank,EmployeeID
| Is This Answer Correct ? | 1 Yes | 3 No |
Post New Answer View All Answers
Explain in brief about Microsoft SQL server?
What guidelines should be followed to help minimize deadlocks?
Why de-normalization required?
What are the different types of triggers in SQL SERVER?
What is checkpoint in sql server?
What can be used instead of trigger?
Explain transaction server auto commit?
What is merge?
What is a fill factor?
What is normalization of database? What are its benefits?
What area unit the various kinds of info compression introduced in sql server 2008?
What are page splits? : SQL Server Architecture
How to display a past time in days, hours and minutes?
explain the difference between oracle- sql and sql server sql ? if both are same y we r using 2 sw.s?
What is the primary use of the model database?
