Find top Nth employee from each department in terms of
salary?
- 13 Answers
- 22057 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
What samples and sample databases are provided by microsoft?
What functions can a view be used to performed?
What is sql server database?
What are the different types of upgrades that can be performed in sql server?
What are the three different part of rdl file explain them?
Do you know what are the differences between lost updates and uncommitted dependencies?
Can you insert NULL in unique column?
What is the difference between update lock and exclusive lock?
What is the default Port No on which SQL Server listens?
How to create a store procedure with encryption?
How to scale out a federation by Sql statement?
What is the optimization being performed in oracle and SQL Server?
What stored by the tempdb ? : sql server database administration
What is dirty read?
Explain transaction server consistency?
