Interested to Buy Any Domain ? << Click Here >> for more details...
Find top Nth employee from each department in terms of
salary?
- 13 Answers
- 26181 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
how many type of indexing in database?
How you can get the list of largest tables in a database?
What is 4nf in normalization form?
Issues related in upgrading SQL Server 2000 to 2005 / 2008
Do you know how to store and query spatial data?
What is the use of partition by in sql server?
What is the cartesian product of the table?
Do you know what is xpath?
Explain about extended stored procedure?
Does dbcc checkdb requires db to be in single_user mode? : sql server database administration
what do you understand by change data capture?
How is SQL Azure different than SQL server?
between cast and convert which function would you prefer and why?
What is meant by dirty read?
How to create user messages with print statements in ms sql server?
