SELECT
DimDepartment.DepartmentName, COUNT(EmployeeKey) As Total,RANK() OVER (ORDER BY COUNT(EmployeeKey) DESC) AS 'Rank_Dep',DENSE_RANK() OVER (ORDER BY COUNT(EmployeeKey) DESC) AS 'Rank_Emp'FROM DimEMployeeJOIN DimDepartment ON DimDepartment.DepartmentKey = Dimemployee.DepartmentKeyGROUP BY DimDepartment.DepartmentNameORDER
In the above table 'Human Resource' and 'Axapta' has same total hence they are positioned in 16, but the next row operation displays position as 18 and 17 for Rank( ) and Dense_Rank( ) function. Hence dense rank is continuous numbering whereas ranking counts the tie rows for numbering and Jumps to next. BY COUNT(EmployeeKey)DESC
No comments:
Post a Comment