SQL Reference Guide

ROW_NUMBER function

Syntax

ROW_NUMBER ( )

Description

ROW_NUMBER is a member of a collection of ranking functions and returns a temporary value calculated when the query is run. It returns the sequential number of a row within a partition of a result set. The values start with 1 for the first row in each partition.

Example

Given this 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;

ROW_NUMBER() over ( [PARTITION BY value_expression, ... [n]] order_by_clause;

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

C1 C2 ROW_NUMBER()

-- -- ------------

1 1 1

1 2 2

1 2 3

1 3 4

2 1 1

2 1 2

2 2 3

2 3 4

Notes

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