Search This Blog

2010-04-13

Rank Function in SQL Server

SQL Server 2005 introduced new ranking functions. This article is 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.

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.

Sample Table:

This will serve as our sample table for all our examples.

sales_employee
nameterritorysales_amount
AX100
BX200
CX200
DX300
EX400
FY300
GY300
HY500
IY600
JZ200
KZ700


Syntax and Examples:

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

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


nameterritorysales_amountROW_NUMBERRANKDENSE_RANKNTILE
AX1001111
BX2002221
CX2003221
DX3005532
EX4008843
FY3006532
GY3007533
HY5009953
IY600101064
JZ2004222
KZ700111174


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

nameterritorysales_amountROW_NUMBERRANKDENSE_RANKNTILE
AX1001111
BX2002221
CX2003222
DX3004433
EX4005544
FY3001111
GY3002112
HY5003323
IY6004434
JZ2001111
KZ7002222

No comments: