ROWNO Predicate (rowno_predicate) 

The ROWNO predicate (rowno_predicate) is a predicate that restricts the number of rows in a result table or the number of rows to be changed or dropped.

Syntax

<rowno_predicate> ::= ROWNO < <unsigned_integer>
| ROWNO < <parameter_spec>
| ROWNO <= <unsigned_integer>
| ROWNO <= <parameter_spec>

Examples

SQL Tutorial, Selecting and Arranging Rows

Explanation

A ROWNO predicate may only be used in a WHERE clause that belongs to a QUERY, DELETE, or UPDATE statement. The ROWNO predicate can be used in the WHERE clause like any other predicate if the following restrictions are observed:

●     The ROWNO predicate must be linked to the other predicates by a logic AND.

●     The ROWNO predicate must not be negated

●     The ROWNO predicate may not be used more than once in the WHERE clause

You can specify the maximum number of rows in the result table using an unsigned integer (unsigned_integer) or a parameter specification (parameter_spec). If more lines are found, they are simply ignored and do not lead to an error message. Specifying a ROWNO predicate of the type ROWNO <= 0 results in an empty result table or no changed or deleted rows.

If a ROWNO predicate and an ORDER clause are specified in a QUERY statement, only the first n result rows are searched and sorted. The result usually differs from that which would have been obtained if a ROWNO predicate had not been used and if the first n result rows had been considered.

If a ROWNO predicate and a set function are specified in a QUERY statement, the set function is only applied to the number of result rows restricted by the ROWNO predicate.

It is useful to specify a ROWNO predicate in an UPDATE or DELETE statement if so many rows have to be processed that, as a result, the log information would exceed the available space. Consequently, you can execute the required change statements in several substeps until the number of changed or deleted rows is less than the number specified in the ROWNO predicate.

If you use a LIMIT clause or a TOP syntax element, you cannot use the ROWNO predicate.