Neoview SQL Reference Manual (R2.4)

RANK/RUNNINGRANK Function
The RUNNINGRANK function is a sequence function that returns the rank of the given value
of an intermediate result table ordered by a SEQUENCE BY clause in a SELECT statement. RANK
is an alternative syntax for RUNNINGRANK.
RUNNINGRANK is a Neoview extension.
RUNNINGRANK(expression) | RANK(expression)
expression
specifies the expression on which to perform the rank.
RUNNINGRANK returns the rank of the expression within the intermediate result table. The
definition of rank is as follows:
RANK = 1 for the first value of the intermediate result table.
= the previous value of RANK if the previous value of expression is the
same as the current value of expression.
= RUNNINGCOUNT(*) otherwise.
In other words, RANK starts at 1. Values that are equal have the same rank. The value of RANK
advances to the relative position of the row in the intermediate result when the value changes.
Considerations for RANK/RUNNINGRANK
Depends of the Sequence order
The RUNNINGRANK function is meaningful only when the given expression is the leading
column of the SEQUENCE BY clause. This is because the RUNNINGRANK function assumes
that the values of expression are in order and that like values are contiguous. If an ascending
order is specified for expression in the SEQUENCE BY clause, then the RUNNINGRANK function
assigns a rank of 1 to the lowest value of expression. If a descending order is specified for
expression in the SEQUENCE BY clause, then the RUNNINGRANK function assigns a rank of
1 to the highest value of expression.
NULL Values
For the purposes of RUNNINGRANK, NULL values are considered to be equal.
Examples of RANK/RUNNINGRANK
Suppose that SEQFCN has been created as:
CREATE TABLE cat.sch.seqfcn
(I1 INTEGER,I2 INTEGER);
The table SEQFCN has columns I1 and I2 with data:
I2II1
1001
2003
1004
2002
3005
null10
null6
2008
RANK/RUNNINGRANK Function 427