ROW_NUMBER

ROW_NUMBER orders the members of a dimension based on the values of an expression. The function returns the sequence numbers of the dimension members.

ROW_NUMBER assigns a unique rank to each dimension member; for identical values, the rank is arbitrary. For example, ROW_NUMBER will always return 1, 2, 3, 4, 5 for a series of five dimension members, even if they all have the same value.

Return Value

NUMBER

Syntax

ROW_NUMBER (ntile_expression) OVER HIERARCHY
     ( dimension | hierarchy
       ORDER BY expression [ASC | DESC] [NULLS {FIRST | LAST}]...
       WITHIN {PARENT | LEVEL | ANCESTOR AT LEVEL level}
     )

Arguments

ntile_expression is not implemented in this function.

dimension is the dimension whose members are being ranked.

hierarchy is a hierarchy of dimension. Otherwise, the default hierarchy is used.

ORDER BY provides the basis for the ranking. You can provide additional ORDER BY clauses to break any ties in the order.

expression provides the values to use as the basis for the rankings.

ASC creates the ranking from smallest to largest data values.

DESC creates the ranking from largest to smallest data values.

NULLS FIRST lists the members with null values first.

NULLS LAST lists the members with null values last.

WITHIN selects a set of related dimension members to be ranked.

PARENT ranks members at the same level with the same parent.

LEVEL ranks all members at the same level.

ANCESTOR AT LEVEL level ranks all members at the same level and with the same parent at a specified level.

Example

The following example ranks customers by level within each region based on the values of the UNITS measure.

ROW_NUMBER () OVER HIERARCHY (GLOBAL.CUSTOMER.SHIPMENTS ORDER BY GLOBAL.UNITS_CUBE.UNITS DESC NULLS LAST WITHIN ANCESTOR AT LEVEL GLOBAL.CUSTOMER.SHIPMENTS.REGION)

Related Topics

AVERAGE_RANK, DENSE_RANK, RANK

Copyright © 2003, 2007, Oracle. All rights reserved.