row_number [Oracle analytical function]

row_number() over (order by col_1 [, col_2 ...])
row_number() over (partition by col_n [, col_m ... ] order by col_1 [, col_2 ...])

row_number() returns an integer greater or equal to one.

Without partition clause

If there is no partition clause, the entire result set is ordered by the columns indicated with the order by clause:

create table row_number_test (
  a number,
  b varchar2(20)
);

insert into row_number_test values (22, 'twenty two');
insert into row_number_test values ( 1, 'one');
insert into row_number_test values (13, 'thirteen');
insert into row_number_test values ( 5, 'five');
insert into row_number_test values ( 4, 'four');

select 
  a, b, row_number() over (order by b) 
from 
  row_number_test
order
  by a;

This query results in the following result set:

         A B                    ROW_NUMBER()OVER(ORDERBYB)
---------- -------------------- --------------------------
         1 one                                           3
         4 four                                          2
         5 five                                          1
        13 thirteen                                      4
        22 twenty two                                    5

As can be seen, the result set is ordered by a (as mandated by the order by a part of the query).

row_number, however, orders the query by b and returns one for the lowest (alphabetical) value of b which is five. The second lowest value of b (four) has a corresponding row_number of 2, and so on.

With partition clause

create table row_number_test_2 (
  a number,
  b varchar2(20),
  c char(1)
);

insert into row_number_test_2 values (22, 'twenty two',   '*');
insert into row_number_test_2 values ( 1, 'one',          '+');
insert into row_number_test_2 values (13, 'thirteen',     '*');
insert into row_number_test_2 values ( 5, 'five',         '+');
insert into row_number_test_2 values ( 4, 'four',         '+');

select 
  a, b, row_number() over (partition by c order by b) 
from 
  row_number_test_2
order
  by a;

Here's the output:

         A B                    ROW_NUMBER()OVER(PARTITIONBYCORDERBYB)
---------- -------------------- --------------------------------------
         1 one                                                       3
         4 four                                                      2
         5 five                                                      1
        13 thirteen                                                  1
        22 twenty two                                                2

row_number behaves quite similar, but it first groups the result set by c's value. There are two records having row_number().. = 1: five and thirteen. five is the lowest (alphabetical) value whose column c='+' while thirteen is the lowest value whose column c='*'.

Links

See first rows for an example that uses row_number.

dense_rank vs rank vs row_number.


	René Nyffenegger's collection of things on the web
	René Nyffenegger on Oracle - Most wanted - Feedback - Follow @renenyffenegger