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

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 clausecreate 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.
See also On max and group by .
