DENSE_RANK

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

DENSE_RANK assigns the same minimum rank to identical values, and returns the results in a sequential list. The result may be fewer ranks than values in the series. For example, DENSE_RANK may return 1, 2, 3, 3, 4 for a series of five dimension members.

Return Value

NUMBER

Syntax

DENSE_RANK ( ) OVER (rank_clause)

rank_clause::=

{ DIMENSION dimension_id | HIERARCHY hierarchy_id }
  ORDER BY order_by_clause [, order_by_clause]...
  [ WITHIN { LEVEL 
            | PARENT 
            | 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.

LEVEL ranks all members at the same level.

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

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, using the default Calendar hierarchy. Notice that although two months (JAN-02 and JUL-02) have the same value and the same rank (6), the ranking continues at 7 for JUN-02.

DENSE_RANK() OVER (DIMENSION "TIME" ORDER BY PRICE_CUBE.UNIT_COST DESC NULLS LAST WITHIN ANCESTOR AT DIMENSION LEVEL TIME.CALENDAR_YEAR)

Related Topics

AVERAGE_RANK, RANK, ROW_NUMBER