ROWS

When ROWS is used in a UNION query, the ROWS directive is applied to the unioned set and must be placed after the last SELECT statement.

If a need arises to limit the subsets returned by one or more SELECT statements inside UNION, there are a couple of options:

Use FIRST/SKIP syntax in these SELECT statements — bearing in mind that an ordering clause (ORDER BY) cannot be applied locally to the discrete queries, but only to the combined output.
Convert the queries to derived tables with their own ROWS clauses.
Use parenthesized query expressions with OFFSET/FETCH

The following examples rewrite the examples used in the section about FIRST and SKIP, earlier in this chapter.

Retrieve the first ten names from the output of a sorted query on the PEOPLE table:

SELECT id, name
FROM People
ORDER BY name ASC
ROWS 1 TO 10;

or its equivalent

SELECT id, name
FROM People
ORDER BY name ASC
ROWS 10;

Return all records from the PEOPLE table except for the first 10 names:

SELECT id, name
FROM People
ORDER BY name ASC
ROWS 11 TO (SELECT COUNT(*) FROM People);

And this query will return the last 10 records (pay attention to the parentheses):

SELECT id, name
FROM People
ORDER BY name ASC
ROWS (SELECT COUNT(*) - 9 FROM People)
TO (SELECT COUNT(*) FROM People);

This one will return rows 81-100 from the PEOPLE table:

SELECT id, name
FROM People
ORDER BY name ASC
ROWS 81 TO 100;

Note	`ROWS` can also be used with the `UPDATE` and `DELETE` statements.