ROWNUM pseudocolumn

Any unresolved and unqualified column reference to the ROWNUM pseudocolumn is converted to the OLAP specification ROW_NUMBER() OVER(). This capability does not apply to the Entry plan of the Db2® Warehouse on Cloud managed service.

ROWNUM numbers the records in a result set. The first record that meets the WHERE clause criteria in a SELECT statement is given a row number of 1, and every subsequent record meeting that same criteria increases the row number.

Both ROWNUM and ROW_NUMBER() OVER() are allowed in the WHERE clause of a subselect and are useful for restricting the size of a result set. If you use ROWNUM in the WHERE clause and there is an ORDER BY clause in the same subselect, the ordering is applied before the ROWNUM predicate is evaluated. Similarly, if you use the ROW_NUMBER() OVER() function in the WHERE clause and there is an ORDER BY clause in the same subselect, the ordering is applied before the ROW_NUMBER() OVER() function is evaluated. If you use the ROW_NUMBER() OVER() function in the WHERE clause, you cannot specify a window-order-clause or a window-partition-clause.

Before translating an unqualified reference to 'ROWNUM' as ROW_NUMBER() OVER() function, an attempt is made to resolve the reference to one of the following items:

A column within the current SQL query
A local variable
A routine parameter
A global variable

You should avoid using 'ROWNUM' as a column name or a variable name.

Example

The following example retrieves the 20th to the 40th rows of a result set that is stored in a temporary table.

   SELECT TEXT FROM SESSION.SEARCHRESULTS
     WHERE ROWNUM BETWEEN 20 AND 40
     ORDER BY ID

Note that ROWNUM is affected by the ORDER BY clause.