ROWS
Retrieves a slice of rows from an ordered set
SELECT <columns> FROM ... [WHERE ...] [ORDER BY ...] ROWS <valueexpression> [TO <valueexpression>]
Argument  Description 

valueexpression 
Any integer expressions 
Note

ROWS is nonstandard syntax

ROWS
limits the amount of rows returned by the SELECT
statement to a specified number or range.
The ROWS
clause also does the same job as the FIRST
and SKIP
clauses, but neither are SQLcompliant.Unlike FIRST
and SKIP
, and OFFSET
and FETCH
, the ROWS
and TO
clauses accept any type of integer expression as their arguments, without parentheses.Of course, parentheses may still be needed for nested evaluations inside the expression, and a subquery must always be enclosed in parentheses.
Important


Characteristics of Using ROWS m
Without a TO
Clause:
Calling ROWS m
retrieves the first m records from the set specified.

If m is greater than the total number of records in the intermediate data set, the entire set is returned

If m = 0, an empty set is returned

If m < 0, the
SELECT
statement call fails with an error
Characteristics of Using ROWS m
With a TO
Clause:
Calling ROWS m TO n
retrieves the rows from the set, starting at row m and ending after row n — the set is inclusive.

If m is greater than the total number of rows in the intermediate set and n >= m, an empty set is returned

If m is not greater than n and n is greater than the total number of rows in the intermediate set, the result set will be limited to rows starting from m, up to the end of the set

If m < 1 and n < 1, the
SELECT
statement call fails with an error 
If n = m  1, an empty set is returned

If n < m  1, the
SELECT
statement call fails with an error
Not Possible to Use a TO
Clause Without a ROWS
Clause:
While ROWS
is an alternative to the FIRST
and SKIP
syntax, there is one situation where the ROWS
syntax does not provide the same behaviour: specifying SKIP n
on its own returns the entire intermediate set, without the first n rows.The ROWS … TO
syntax needs a little help to achieve this.
With the ROWS
syntax, you need a ROWS
clause in association with the TO
clause and deliberately make the second (n) argument greater than the size of the intermediate data set.This is achieved by creating an expression for n that uses a subquery to retrieve the count of rows in the intermediate set and adds 1 to it, or use a literal with a sufficiently large value.