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 always returns 1, 2, 3, 4, 5 for a series of five dimension members, even when they have the same value.

Return Value

NUMBER

Syntax

ROW_NUMBER ( ) OVER (rank_clause)

rank_clause::=

{ DIMENSION dimension_id | HIERARCHY hierarchy_id }
  ORDER BY order_by_clause [, order_by_clause]...
  [ WITHIN { PARENT 
            | LEVEL 
            | ANCESTOR AT { DIMENSION LEVEL dim_lvl_id
                           | HIERARCHY LEVEL hier_level_id
                           }
            }
  ]

order_by_clause::=

expression [ASC | DESC] [NULLS {FIRST | LAST}] 

Arguments

dimension_id

The dimension over which the values are calculated using the default hierarchy.

hierarchy_id

The hierarchy over which the values are calculated. If dimension_id is used instead, 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 | DESC

Sorts the ranking from smallest to largest (ascending) or from largest to smallest (descending).

NULLS {FIRST | LAST}

Determines whether members with null values are listed first or 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 ranks all members at the same level and with the same ancestor at a specified level.

dim_level_id

The name of a level of dimension_id.

hier_level_id

The name of a level of hierarchy_id.

Example

This example ranks time periods within a calendar year by Unit Cost. Notice even though two months (JAN-02 and JUL-02) have the same value, they are assigned sequential numbers (6 and 7).

ROW_NUMBER() OVER (HIERARCHY TIME.CALENDAR ORDER BY PRICE_CUBE.UNIT_COST DESC NULLS LAST WITHIN ANCESTOR AT DIMENSION LEVEL TIME.CALENDAR_YEAR)

Related Topics

AVERAGE_RANK, DENSE_RANK, RANK