Labels

Apache Hadoop (3) ASP.NET (2) AWS S3 (2) Batch Script (3) BigQuery (21) BlobStorage (1) C# (3) Cloudera (1) Command (2) Data Model (3) Data Science (1) Django (1) Docker (1) ETL (7) Google Cloud (5) GPG (2) Hadoop (2) Hive (3) Luigi (1) MDX (21) Mongo (3) MYSQL (3) Pandas (1) Pentaho Data Integration (5) PentahoAdmin (13) Polybase (1) Postgres (1) PPS 2007 (2) Python (13) R Program (1) Redshift (3) SQL 2016 (2) SQL Error Fix (18) SQL Performance (1) SQL2012 (7) SQOOP (1) SSAS (20) SSH (1) SSIS (42) SSRS (17) T-SQL (75) Talend (3) Vagrant (1) Virtual Machine (2) WinSCP (1)

Monday, January 24, 2011

Difference between Rank and Dense_Rank in SQL



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