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

name	territory	sales_amount
A	X	100
B	X	200
C	X	200
D	X	300
E	X	400
F	Y	300
G	Y	300
H	Y	500
I	Y	600
J	Z	200
K	Z	700

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

name	territory	sales_amount	ROW_NUMBER	RANK	DENSE_RANK	NTILE
A	X	100	1	1	1	1
B	X	200	2	2	2	1
C	X	200	3	2	2	1
D	X	300	5	5	3	2
E	X	400	8	8	4	3
F	Y	300	6	5	3	2
G	Y	300	7	5	3	3
H	Y	500	9	9	5	3
I	Y	600	10	10	6	4
J	Z	200	4	2	2	2
K	Z	700	11	11	7	4

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

name	territory	sales_amount	ROW_NUMBER	RANK	DENSE_RANK	NTILE
A	X	100	1	1	1	1
B	X	200	2	2	2	1
C	X	200	3	2	2	2
D	X	300	4	4	3	3
E	X	400	5	5	4	4
F	Y	300	1	1	1	1
G	Y	300	2	1	1	2
H	Y	500	3	3	2	3
I	Y	600	4	4	3	4
J	Z	200	1	1	1	1
K	Z	700	2	2	2	2