Sunday, December 02, 2007

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


No comments: