Understanding Oracle Analytical Function: row_number()

Purpose
-------
 Understanding Oracle analytical function: row_number()

Syntax
------
 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



Test steps
-----------

1. Without partition 

 *** Run script: row_number_noprttn.tst from sqlplus

 scott@ORCL> ho cat row_number_noprttn.tst

 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;

 drop table row_number_test;

 *** Results:

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


 *** Notes: the value of ROW_NUMER col is obtained by order by B
 whith five the lowest and twenty two the highest


2. With partition

 *** Run script: row_number_prttn.tst from sqlplus

 scott@ORCL> ho cat row_number_prttn.tst
 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;

 drop table row_number_test_2;

 *** Output

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

 *** Notes:
      In this case, there are two partitions resulted by partition by c
      and the row_number col value starts from 1 in each partition