skip to main content
Functions : Scalar functions : RANK
 

RANK

Syntax 

RANK() OVER([PARTITION BY value] ORDER BY value)

Description

This function returns the rank of all rows. If a partition is specified, it returns the rank of all rows and divides the result set into partitions. The rank is 1 + the number of ranks that occur before the specified row. If two or more rows qualify for the same rank in the same partition, each of those rows receives the same rank. Because multiple rows can receive the same rank, this function does not always return a sequential set of ranks. The sort order of the SELECT statement determines the order in which the rows appear in the result, so a row with a rank of 1 may not be the first row in the partition.
The OVER clause determines the partitioning and ordering of the rows in the result before the RANK function is applied using the following clauses:
• The PARTITION BY clause divides the result set produced by the FROM clause into partitions to which the RANK function is applied. This clause is optional.
• The ORDER BY clause determines the order in which RANK values are applied to the rows. An integer cannot represent a column in the ORDER BY clause.
value_expr must refer to columns made available by the FROM clause in the SELECT statement. It can be any of the following values:
• Column in the select list
• Scalar subquery
• Scalar function
• User-defined variable
It cannot refer to expressions or aliases.

Example 

Example A
SELECT RANK() OVER(ORDER BY Age) AS RankByAge, FirstName, Age
FROM Person
This statement returns the following results:
 
RankByAge
FirstName
Age
1
2
2
4
4
6
7
7
9
10
11
Larry
Doris
George
Mary
Sherry
Sam
Ted
Marty
Sue
Frank
John
5
6
6
11
11
17
23
23
29
38
40
Because the rank is ordered by age, when rows have the same Age value, their RankByAge value is the same. Notice that all the RankByAge values are not sequential. For example, although Doris and George both have the same RankByAge value of 2, the rank value for Mary (the next unique Age) does not have a rank value of 3, but instead has a rank value of 4. This is because Mary is the fourth row returned in the result.
Example B
SELECT RANK() OVER(PARTITION BY Gender ORDER BY Age) AS PartitionByGender,
FirstName, Age, Gender
FROM Person
This statement returns the following results:
 
PartitionByGender
FirstName
Age
Gender
1
2
2
4
1
2
3
4
4
6
7
Doris
Mary
Sherry
Sue
Larry
George
Sam
Ted
Marty
Frank
John
5
11
11
29
5
6
17
23
23
38
40
F
F
F
F
M
M
M
M
M
M
M
In this example, the PARTITION BY clause allows the SELECT statement to return multiple rank values, where each rank value is for a specific group. The rank values are grouped by Gender and each rank value is ordered by Age. Notice that the rank values start over at 1 when the first M gender is encountered.