SQL Server Ranking Functions

SQL Server 2005 introduced new ranking functions. This article is tailored as an introduction to these functions, difference between them and when to use each and a few examples.

Ranking What:

The new ranking functions are new internal functions to SQL Server 2005/2008.
From MSDN “Ranking functions return a ranking value for each row in a partition. Depending on the function that is used, some rows might receive the same value as other rows. Ranking functions are nondeterministic.”
In simple terms, ranking functions allow you to sequentially number your result set. Your result set can be partitioned so the numbering essentially resets for each partition for example you can get the sales rank of employees partitioned by their department, or manager etc..
What’s worth mentioning is that ranking functions are non-deterministic so you cannot use them in something like an indexed view.

Syntax and Examples:

ROW_NUMBER () OVER ( [ <partition_by_clause> ] <order_by_clause> )

Returns the row number of the result set for each row in a partition based on the order provided in the order by clause.

RANK () OVER ( [ <partition_by_clause> ] <order_by_clause> )

Similar to Row_Number() only Rank determines the position, or lack for a better word, ranking of each row based on the Order By clause. Rank is usually used with the Partition clause to cluster your result sets. Rank also skips numbers, if 2 or more records tie in value, they will receive the same rank. The following rank would 1+ the total number of records in the same partition so for example (1,2,2,2,5,6)

DENSE_RANK () OVER ( [ <partition_by_clause> ] <order_by_clause> )

Same as Rank() only guarantees consecutive integers (No skipping) (1, 2, 2, 2, 2, 3). If a tie occurs, it will sort arbitrarily (based on the execution plan and indexes used) and continue.

NTILE (integer_expression) OVER ( [ <partition_by_clause> ] <order_by_clause> )

Used to distribute the rows in an ordered partition into x number of groups. Each row receives the group number it belongs to.

Example: Simple ORDER BY clause

SELECT
       name
      ,territory
      ,ROW_NUMBER() OVER ( ORDER BY sales_amount ) AS [row_number]
      ,RANK() OVER ( ORDER BY sales_amount ) AS [rank]
      ,DENSE_RANK() OVER ( ORDER BY sales_amount ) AS [dense_rank]
      ,NTILE(4) OVER ( ORDER BY sales_amount ) AS [ntile]
FROM
      sales_employee

Example: Using PARTITION BY

SELECT
name
,territory
,ROW_NUMBER() OVER (PARTITION BY territory ORDER BY sales_amount) 
AS [row_number]
,RANK() OVER (PARTITION BY territory ORDER BY sales_amount) 
AS [rank]
,DENSE_RANK() OVER (PARTITION BY territory ORDER BY sales_amount) 

AS [dense_rank]
,NTILE(4) OVER (PARTITION BY territory ORDER BY sales_amount) 
AS [ntile] FROM sales_employee

Conclusion:
Ranking functions can come in very handy when devising groups or sequential 
numbering of your result sets. Ranking functions are particularly useful in reporting.