OLAP numbering function expression

The OLAP numbering function expression returns a sequential number for each row in the result set of a single query.

The OLAP numbering function expression is an OLAP window expression that you can include in the Projection list of a SELECT statement, or the ORDER BY clause of a SELECT statement.

Syntax

OVER clause for the numbering function

Notes:

¹ See OVER clause for OLAP window expressions

Usage

The keywords ROW_NUMBER and ROWNUMBER are synonyms for the same function. This numbering function is like a simplified RANK function that does not require a window ORDER clause and that does not detect duplicate values. The ROW_NUMBER function always returns a unique value for each row in each OLAP window partition.

The ROW_NUMBER function takes no argument, but you must include the empty parentheses after the ROW_NUMBER (or ROWNUMBER) keyword.

The ROW_NUMBER function returns an unsigned integer for every row in each OLAP partition. The sequence of row numbers in each partition starts with 1, and each successive row is incremented by 1, whether consecutive rows in a window partition have the same or different column values.

If the window PARTITION clause is not specified, the complete result set is numbered from 1 to n, where n is the number of qualifying rows that the query or subquery returns.

The OLAP window that the OVER clause defines for numbering functions has this syntax:

The window PARTITION clause is optional. If none is specified, the scope of the numbering function is the entire result set of the query or subquery, rather than partitioned subsets.
The window ORDER clause is optional. If none is specified, the returned row numbers are based on the default order of the rows as processed by the query. If a window ORDER clause is specified, the ORDER BY specification determines the row number assignments.
The window Frame clause is not supported for OLAP numbering functions.

If the OVER clause that defines the OLAP window for the ROW_NUMBER function omits both the window PARTITION clause and window ORDER clause, you must include the empty parentheses after the OVER keyword.

Example: ROW_NUMBER function

The following query partitions the rows in the product table by distinct package types (pkg_type) and assigns row numbers that restart at 1 for each partition.

SELECT ROW_NUMBER() 
   OVER(PARTITION BY pkg_type ORDER BY prod_name)
   AS rownum, prod_name, pkg_type 
FROM product;

ROWNUM PROD_NAME                      PKG_TYPE            
     1 Aroma Sounds CD                Aroma designer box  
     2 Aroma Sounds Cassette          Aroma designer box  
     1 Christmas Sampler              Gift box            
     2 Coffee Sampler                 Gift box            
     3 Easter Sampler Basket          Gift box            
     4 Spice Sampler                  Gift box            
     5 Tea Sampler                    Gift box            
     1 Aroma Roma                     No pkg              
     2 Aroma baseball cap             No pkg              
     3 Aroma t-shirt                  No pkg              
     4 Assam Gold Blend               No pkg              
     5 Assam Grade A                  No pkg              
     6 Breakfast Blend                No pkg              
     7 Cafe Au Lait                   No pkg              
     8 Coffee Mug                     No pkg              
     9 Colombiano                     No pkg              
    10 Darjeeling Number 1            No pkg              
    11 Darjeeling Special             No pkg              
    12 Demitasse Ms                   No pkg              
    13 Earl Grey                      No pkg              
...

Because the window ORDER clause specifies the prod_name column as the sorting key, in the default (ASC) order, the returned row numbers are based on the alphabetical order of the product names. For example, the three "Aroma" products are numbered 1, 2, and 3 within the "No pkg" partition. (Uppercase letters sort above lowercase in the default ASCII collation.)