Rank Operations

Rank Clause

Order of tuples is important in the sets processed by MDX. To preserve order, you need to generate a RANK column for every set. That 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:

<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, then 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, then 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, then the value is row count. If LEVELS is specified, then it is level count.
  
  
• If a name for the new column is not specified (using the AS <column_name> clause), then 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 the top x rows be picked from each group (according to some criteria). As always, there is the requirement that the ordering be preserved. Clearly, what is required is an enhanced RANK clause that has the ability to pick the top x rows. This is achieved by the RANKTOP clause.

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

<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. That is, their output is a table. Therefore, if a query expression has more than one RANK or RANKTOP clause, then they are evaluated in the order of appearance. That is, 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, in the 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

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