|
Ranking Functions with Partition
By Kalman Toth, M.Phil., M.Phil., MCDBA, MCTIP
June 5, 2005
Microsoft SQL Server 2005 introduces ranking and ntiling (like
quartertile) functions augmenting already existing statistical
functions in SQL Server 2000. In the following example ROW_NUMBER
generates a sequence on BaseRate (hourly wage) in descending order
partitioned for each department. The report is sorted by department
name. The ranking column shows what is the ranking salary wise.
The top 3 salaried staff is selected from each department, more
if there is a tie.
use AdventureWorks
select Department, Employee, Title, BaseRate, Rank
from
(select Department=d.Name, Employee=c.FirstName+'
'+c.LastName, e.Title, e.BaseRate,
Rank=ROW_NUMBER()OVER (PARTITION BY e.DepartmentID
order by e.BaseRate desc)
from Person.Contact c JOIN HumanResources.Employee
e
on c.ContactID=e.ContactID JOIN HumanResources.Department
d
on e.DepartmentID=d.DepartmentID) r
where r.Rank <= 3
order by r.Department,r.BaseRate desc
This is the partial result set:
Department Employee Title HourlyWage Ranking
Document Control Zainal Arifin Document Control Manager 17.7885
1
Document Control Tengiz Kharatishvili Control Specialist 16.8269
2
Document Control Chris Norred Control Specialist 16.8269 3
Engineering Terri Duffy Vice President of Engineering 63.4615
1
Engineering Roberto Tamburello Engineering Manager 48.7282 2
Engineering Michael Sullivan Senior Design Engineer 37.5145 3
Executive Ken Sánchez Chief Executive Officer 125.50 1
Executive Laura Norman Chief Financial Officer 60.0962 2
Facilities and Maintenance Gary Altman Facilities Manager 24.0385
1
Facilities and Maintenance Christian Kleinerman Maintenance Supervisor
20.4327 2
Facilities and Maintenance Magnus Hedlund Facilities Administrative
Assistant 9.75 3
Finance Wendy Kahn Finance Manager 43.2692 1
Finance David Liu Accounts Manager 34.7356 2
Finance Barbara Moreland Accountant 26.4423 3
Human Resources Paula Barreto de Mattos Human Resources Manager
27.1394 1
Human Resources Willis Johnson Recruiter 18.2692 2
Human Resources Vidur Luthra Recruiter 18.2692 3
Information Services Jean Trenary Information Services Manager
50.4808 1
Information Services Stephanie Conroy Network Manager 39.6635
2
Information Services François Ajenstat Database Administrator
38.4615 3
Marketing David Bradley Marketing Manager 40.5912 1
Marketing Kevin Brown Marketing Assistant 14.5711 2
Marketing Sariya Harnpadoungsataya Marketing Specialist 14.4231
3
Production James Hamilton Vice President of Production 84.1346
1
Production Lynne Dobney Production Supervisor - WC60 27.0608 2
Production Taylor Maxwell Production Supervisor - WC50 25.00 3
Production Control Peter Krebs Production Control Manager 24.5192
1
Production Control A. Scott Wright Master Scheduler 23.5577 2
Production Control Sairaj Uddin Scheduling Assistant 16.00 3
Purchasing Sheela Word Purchasing Manager 30.00 1
Purchasing Ben Miller Buyer 18.2692 2
Purchasing Erin Hagens Buyer 18.2692 3
Quality Assurance Hazem Abolrous Quality Assurance Manager 28.8462
1
Quality Assurance Peng Wu Quality Assurance Supervisor 21.6346
2
Quality Assurance Mark Harrington Quality Assurance Technician
10.5769 3
Research and Development Dylan Miller Research and Development
Manager 52.5202 1
Research and Development Michael Raheem Research and Development
Manager 44.197 2
Research and Development Gigi Matthew Research and Development
Engineer 42.5164 3
Sales Brian Welcker Vice President of Sales 72.1154 1
Sales Amy Alberts European Sales Manager 48.101 2
Sales Syed Abbas Pacific Sales Manager 48.101 3
Shipping and Receiving Pilar Ackerman Shipping and Receiving Supervisor
19.2308 1
Shipping and Receiving Matthias Berndt Shipping and Receiving
Clerk 9.50 2
Shipping and Receiving Vamsi Kuppa Shipping and Receiving Clerk
9.50 3
Tool Design Rob Walters Senior Tool Designer 33.6117 1
Tool Design Ovidiu Cracium Senior Tool Designer 30.0116 2
Tool Design Thierry D'Hers Tool Designer 28.154 3
|
