RANK

The RANK function computes the rank of values in a numeric expression.

Return Value

DECIMAL

Syntax

RANK(expression method [attributes] [BASEDON dimension-list])

where attributes is one or more of the following:

RESET

NAFIRST

NALAST

LIMITSAVE (limit-expression...)

TIEBREAKERS (target-expression...)

Arguments

expression

The numeric expression for which rankings are to be computed.

method

The method to use in computing the rank of the values in expression. The method argument can be one of the following keywords. See also "Results of Method Values".

Table 8-4 Methods for Computing RANK

Method	Description
MIN	Identical values get the same minimum rank.
MAX	Identical values get the same maximum rank.
AVERAGE	Identical values get the same average rank.
PACKED	Identical values get the same rank but the results are packed into consecutive INTEGER values.
UNIQUE	All values get a unique rank; for identical values the rank is arbitrary.
PERCENTILE	Values are ranked from 1 to 100, based on the relative frequency of their occurrence in the expression.
DECILE	Values are ranked from 1 to 10, based on the relative frequency of their occurrence in the expression.
QUARTILE	Values are ranked from 1 to 4, based on the relative frequency of their occurrence in the expression.

RESET

Changes how Oracle OLAP computes RANK within a looping statement (for example, an assignment statement):

• When you do not specify RESET, Oracle OLAP ranks the members of each group only once and, then caches those ranked values. As the looping statement continues to execute and RANK executes against same set of values, Oracle OLAP uses those cached values to return values for RANK.

• When you include RESET, Oracle OLAP recomputes RANK each and every time it executes within the looping statement. This behavior significantly increases the time it takes for Oracle OLAP to execute the looping statement that contains RANK.

Within a looping statement, the only time you use RANK with RESET is when you know that within any group the rankings of members within that group will change during the execution of the looping statement.

NAFIRST

Specifies that Oracle OLAP converts all NA values to the largest positive decimal number or (10**308) before ranking the values.

Note:

An NA expression value produces an NA rank unless you specify either NAFIRST or NALIST.

NALAST

Specifies that Oracle OLAP converts all NA values to the largest negative decimal number or -(10**308) before ranking the values.

Note:

An NA expression value produces an NA rank unless you specify either NAFIRST or NALIST.

LIMITSAVE

Specifies the status of the dimensions that Oracle OLAP uses when calculating RANK. By specifying LIMITSAVE within the RANK function, rather than specify CHGDIMS with LIMITSAVE, you insure that Oracle OLAP evaluates the status only once when RANK needs to calculate a new result.

limit-expression

The dimension values that Oracle OLAP uses to determine dimension status while executing RANK. For the limit-expression argument, you can specify any expression including a valueset, a LIMIT function, or a SORT function.

TIEBREAKERS

Specifies how Oracle OLAP sequences values of equal rank.

tiebreaker-expression

Any expression including a valueset, a LIMIT function, or a SORT function. Oracle OLAP executes the tiebreaker-expressions in the order in which they are specified. The status of the dimensions of each tiebreaker-expression is the current status of the dimensions or the status specified in the LIMITSAVE clause, if any.

Note:

When you specify a valueset for tiebreaker-expression, Oracle OLAP returns the ranked items in -(STATRANK) order.

BASEDON dimension-list

An optional list of one or more of the dimensions of expression to include in the ranking. When you do not specify the dimensions, then RANK bases the ranking on all of the dimensions of expression.

Note:

When the current value of a BASEDON dimension is not in ranking status, Oracle OLAP returns a rank of NA.

Notes

Monitoring the Behavior of RANK

The OLAP DML provides the RANK_CALLS, RANK_CELLS, and RANK_SORTS options that you can use to monitor the behavior of the RANK function.

RANK_CALLS The RANK_CALLS option is an INTEGER, read-only option that holds the number of calls that Oracle OLAP has made to the RANK function.

RANK_CELLS The RANK_CELLS option is an INTEGER, read-only option that holds the number of values that Oracle OLAP has computed when executing the RANK function.

RANK_SORTS  The RANK_SORTS option is a read-only option that holds the number of sorts that have been triggered by the execution of the RANK function

Results of Method Values

This note describes the results of the different methods of ranking values. The results are based on the sales2 variable, which is described in "Ranking Values", with the geography dimension limited to G2 as the following statements demonstrate.

LIMIT geography TO 'G2'
SORT items D sales2
REPORT DOWN geography sales2

The preceding statements produce the following output.

------------------------SALES2------------------------
               ------------------------ITEMS-------------------------
GEOGRAPHY        ITEM4      ITEM2      ITEM3      ITEM1      ITEM5
-------------- ---------- ---------- ---------- ---------- ----------
G2                  25.00      20.00      20.00      15.00       7.00

Table 8-5, "Results of Different Methods of Ranking" shows the results of the different methods of ranking that are produced by a statement of the form

REPORT DOWN geography RANK(sales2 MIN BASEDON items)

with the different method keywords substituted for MIN.

Note that the value that is returned by the UNIQUE method for Item2 and Item3 can be either 2 or 3, since the RANK function randomly assigns a unique rank for identical values in the expression.

Examples

DEFINE geography DIMENSION TEXT 
MAINTAIN geography ADD 'g1' 'g2' 'g3'
DEFINE items DIMENSION TEXT
MAINTAIN items ADD 'Item1' 'Item2' 'Item3' 'Item4' 'Item5'
DEFINE sales2 DECIMAL <geography items>

-------------SALES2-------------
               -----------GEOGRAPHY------------
ITEMS              g1         g2         g3
-------------- ---------- ---------- ----------
Item1               30.00      15.00      12.00
Item2               10.00      20.00      18.00
Item3               15.00      20.00      24.00
Item4               30.00      25.00      25.00
Item5                  NA       7.00      21.00

report rank(sales2 min basedon items)

-RANK(SALES2 MIN BASEDON ITEMS)-
               -----------GEOGRAPHY------------
ITEMS              g1         g2         g3
-------------- ---------- ---------- ----------
Item1                1.00       4.00       5.00
Item2                4.00       2.00       4.00
Item3                3.00       2.00       2.00
Item4                1.00       1.00       1.00
Item5                  NA       5.00       3.00

REPORT RANK(sales2 MIN BASEDON geography)

----RANK(SALES2 MIN BASEDON-----
               -----------GEOGRAPHY)-----------
               -----------GEOGRAPHY------------
ITEMS              g1         g2         g3
-------------- ---------- ---------- ----------
Item1                1.00       2.00       3.00
Item2                3.00       1.00       2.00
Item3                3.00       2.00       1.00
Item4                1.00       2.00       2.00
Item5                  NA       2.00       1.00

REPORT RANK(sales2, MIN)

-------RANK(SALES2, MIN)--------
               -----------GEOGRAPHY------------
ITEMS              g1         g2         g3
-------------- ---------- ---------- ----------
Item1                1.00      10.00      12.00
Item2               13.00       7.00       9.00
Item3               10.00       7.00       5.00
Item4                1.00       3.00       3.00
Item5                  NA      14.00       6.00