RANK() Skips line numbers after a tie in rank eg;
in these results 5, 9, 10, 11, 12 & 15 are missing >
select RANK() OVER (ORDER BY count(*) desc) as DepartmentRank, D.GroupName, D.Name, Count(*) as EmployeeCount from HumanResources.EmployeeDepartmentHistory EDH inner join HumanResources.Department D on EDH.DepartmentID = D.DepartmentID where EndDate is Null group by D.GroupName, D.Name order by count(*) desc;
DENSE_RANK() Does not skips line numbers after a tie in rank.
select DENSE_RANK() OVER (ORDER BY count(*) desc) as DepartmentRank, D.GroupName, D.Name, Count(*) as EmployeeCount from HumanResources.EmployeeDepartmentHistory EDH inner join HumanResources.Department D on EDH.DepartmentID = D.DepartmentID where EndDate is Null group by D.GroupName, D.Name order by count(*) desc;
No comments:
Post a Comment