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. For example, DENSE_RANK may return 1, 2, 3, 3, 5 for a series of five dimension members.

Return Value

NUMBER

Syntax

DENSE_RANK (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.

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

Related Topics

AVERAGE_RANK, RANK, ROW_NUMBER

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