SQL Reference Guide

RANK function

Syntax

RANK() OVER ( [PARTITION BY value_expression, ... [n]] order_by_clause;

Description

RANK returns a temporary value calculated when the query is run. It returns the rank of a row within a partition of a result set. The rank is one plus the number of ranks that come before the row in question. When multiple rows share the same rank, the rank of the next row is not consecutive.

Example

Given the following table definition:

create table ranking (c1 int, c2 int);

insert into ranking values (1,1);

insert into ranking values (1,2);

insert into ranking values (1,3);

insert into ranking values (2,1);

insert into ranking values (2,2);

insert into ranking values (2,3);

commit work;

select c1, c2, rank() over (partition by c1 order by c2) from ranking;

C1 C2 RANK()

-- -- -----

1 1 1

1 2 2

1 3 4

2 1 1

2 2 3

2 3 4

Notes

RANK value is not consecutive for all result sets and can produce gaps in numbering. Use DENSE_RANK for absolute rank numbering.
The PARTITION BY clause distributes the rows in the result set into partitions by one or more value_expression.
Only a single ranking function may be used in a statement.
Ranking functions are nondeterministic as they are computed for each generated result set.
Introduced in V12.0.1