Skip Headers
Oracle® OLAP Expression Syntax Reference
Release 11.2

Part Number E23381-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

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)

Product Time Cost Row Number
Deluxe Mouse MAR-02 24.05 1
Deluxe Mouse APR-02 23.95 2
Deluxe Mouse FEB-02 23.94 3
Deluxe Mouse AUG-02 23.88 4
Deluxe Mouse MAY-02 23.84 5
Deluxe Mouse JAN-02 23.73 6
Deluxe Mouse JUL-02 23.73 7
Deluxe Mouse JUN-02 23.72 8
Deluxe Mouse SEP-02 23.71 9
Deluxe Mouse NOV-02 23.65 10
Deluxe Mouse DEC-02 23.62 11
Deluxe Mouse OCT-02 23.37 12

Related Topics

AVERAGE_RANK, DENSE_RANK, RANK