|
Execute the following
Microsoft SQL Server T-SQL script in SSMS Query Editor to calculate the top 5 salaries for
each department.
To simplify the main SELECT, the query uses triple-nested CTEs for the calculation along with the ROW_NUMBER(), OVER and PARTITION BY new features of SQL Server 2005 T-SQL.
USE AdventureWorks;
DECLARE @n int
SET @n=5
;WITH cteLastRaiseDate (EmployeeID, LastChangeDate)
AS
(
SELECT EmployeeID, MAX(RateChangeDate)
FROM HumanResources.EmployeePayHistory
GROUP BY EmployeeID
),
ctePayRate(EmployeeID, Rate)
AS
(
SELECT eph.EmployeeID, eph.Rate
FROM HumanResources.EmployeePayHistory eph
JOIN cteLastRaiseDate lrd
ON eph.EmployeeID = lrd.EmployeeID
AND eph.RateChangeDate = lrd.LastChangeDate
),
cteSalaryMaxDown as
(
SELECT Department = d.Name, Rate,
[SeqNoMaxDown] = row_number() over (partition by d.DepartmentID order by Rate Desc)
FROM ctePayRate eph
JOIN HumanResources.Employee e
ON eph.EmployeeID = e.EmployeeID
JOIN HumanResources.EmployeeDepartmentHistory edh
ON edh.EmployeeID = e.EmployeeID
JOIN HumanResources.Department d
ON edh.DepartmentID = d.DepartmentID
WHERE EndDate is null)
select Department, Rate='$'+convert(varchar,Rate), SeqNoMaxDown
from cteSalaryMaxDown
where SeqNoMaxDown < @n
Order by Department
Partial Results:
| Department |
Rate |
SeqNoMaxDown |
| Document Control |
$17.79 |
1 |
| Document Control |
$16.83 |
2 |
| Document Control |
$16.83 |
3 |
| Document Control |
$10.25 |
4 |
| Document Control |
$10.25 |
5 |
| Engineering |
$63.46 |
1 |
| Engineering |
$43.27 |
2 |
| Engineering |
$36.06 |
3 |
| Engineering |
$32.69 |
4 |
| Engineering |
$32.69 |
5 |
| Executive |
$125.50 |
1 |
| Executive |
$60.10 |
2 |
| Facilities and Maintenance |
$24.04 |
1 |
|
