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,2);
insert into ranking values (1,3);
insert into ranking values (2,1);
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
See Also