SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Functions and Expressions

HP NonStop SQL/MX Reference Manual—540440-003

9-126

RUNNINGCOUNT Function

The RUNNINGCOUNT function is a sequence function that returns the number of rows

up to and including the current row of an intermediate result table ordered by a

SEQUENCE BY clause in a SELECT statement. See SEQUENCE BY Clause on

page 7-18.

RUNNINGCOUNT is an SQL/MX extension.

as an argument causes RUNNINGCOUNT(*) to return the number of rows in the

intermediate result table up to and including the current row.

column-expression

specifies a derived column determined by the evaluation of the column expression.

If column-expression is the argument, RUNNINGCOUNT returns the number

of rows containing nonnull values of column-expression in the intermediate

result table up to and including the current row.

Considerations for RUNNINGCOUNT

No DISTINCT Clause

The RUNNINGCOUNT sequence function is defined differently from the COUNT

aggregate function. If you specify DISTINCT for the COUNT aggregate function,

duplicate values are eliminated before COUNT is applied. Note that you cannot specify

DISTINCT for the RUNNINGCOUNT sequence function; duplicate values are counted.

Examples of RUNNINGCOUNT

Suppose that SEQFCN has been created as:

CREATE TABLE $db.mining.seqfcn

(I1 INTEGER,TS TIMESTAMP);

Within MXCI, the ANSI alias name has been mapped as:

CREATE SQLMP ALIAS db.mining.seqfcn $db.mining.seqfcn;

RUNNINGCOUNT {(*) | (column-expression)}