MDAC 2.5 SDK - OLE DB Programmer's Reference
Chapter 26: Mapping MDX to SQL Statements

Rank Operations

Both the order of tuples in the sets processed by MDX and the placement level are important for many OLAP operations.

RANK Clause

To preserve order, a RANK column must be generated for every set. This column contains ordering information. The first tuple has a rank value of 1, the second has a rank value of 2, and so on.

Rank values are generated by an extension to SQL: the RANK clause. This clause occurs at the end of a query expression (such as a SELECT statement). The input to this clause is a table, and its output is also a table. The syntax is as follows:

<rank_clause> ::= 
                  query_expression
                  RANK ROWS | LEVELS [AS <column_name>] 
                  [RANKORDER BY <sort_specification_list>]
                  [RANKGROUP BY <grouping_column_reference_list>]

The semantics of this clause are as follows:

• If a RANKGROUP BY clause is present, the set of incoming rows is divided into groups (according to the RANKGROUP BY clause). Otherwise, there is only one group. The following steps are performed on each group:
  1. If a RANKORDER BY clause is present, the rows are sorted accordingly.
     
     
  2. Rows are counted in their sort order (if any) by using two counters: row count and level count. Both counters start at 0. The first row increments both counts. Every input row increments row count by 1. The level count is incremented by 1 if a sort specification list is present and an input row differs from its preceding row in at least one element of the sort specification list.
• The value for the new column (the one specified in <column_name> above) depends on whether ROWS or LEVELS was specified. If ROWS was specified, the value is row count. If LEVELS is specified, the value is level count.
  
  
• If a name for the new column is not specified (using the AS <column_name> clause), its name is RANK.
  
  
• There is one output row for every input row. The output rows have the same columns as the input rows and have the extra rank column whose value is as described above.

RANKTOP Clause

Many OLAP operations require that a set of input tuples be separated into groups and that the top x rows be picked from each group (according to some criteria). As always, it is required that the ordering be preserved. The RANKTOP clause has the ability to pick the top x rows.

This clause occurs at the end of a query expression (that is, a SELECT statement). The input to this clause is a table, and its output is also a table. The syntax is as follows:

<ranktop_clause> ::= 
                     <query_expression>
                     RANKTOP <numeric_value_expression> ROWS 
                        | LEVELS [AS <column_name>]
                     [RANKORDER BY <sort_specification_list>] 
                     [RANKGROUP BY <grouping_column_reference_list>]

The semantics of this clause are as follows:

• The first five items of semantics description are the same as the first four items of the RANK clause.
  
  
• An output row is produced if the value of the new column (the one specified in <column_name>) is less than or equal to <numeric_value_expression>.The output rows have the same columns as the input rows and have the extra RANK column whose value is as described above.

Multiple RANK and RANKTOP Clauses

Both RANK and RANKTOP clauses are part of a query expression; their output is a table. Therefore, if a query expression has more than one RANK or RANKTOP clause, they are evaluated in the order of appearance. The first clause results in a table that is used as the input to the second clause, which is in turn used as an input to the third clause, and so on.

For example, consider the following query:

SELECT Name, Newrank AS Rank
FROM 
         (((SELECT Name, "1" AS Src, Rank FROM S1
         UNION [ALL]
         SELECT Name, "2" AS Src, Rank FROM S2)
      RANKTOP 1 ROWS RANKORDER BY Src, Rank RANKGROUP BY Name)
   RANK ROWS AS NewRank RANKORDER BY Src, Rank)
ORDER BY Rank

In above example, the innermost query expression is used as an input to the RANKTOP clause. The output of the RANKTOP clause, which is again a table (indented two levels above), is used as an input to the RANK clause. The output of the RANK clause is also a table (indented one level above), which is used as input to the FROM clause.