Tuesday, 17 April 2007

SQL 2005 : RANK() vs DENSE_RANK()

Using AdventureWorks to demonstrate RANK() vs DENSE_RANK() >

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: