Accessing part of a result set based on data position

Data-dependent or numeric-based pagination can be used to retrieve a subset of data from a result set based on the position of the data.

• For data dependent pagination: Use row-value expressions with the <, <=, >, or >= comparison operators in a SELECT statement to retrieve only part of a result set.
  When used with a basic predicate, row-value expressions enable an application to access only part of a Db2 result table based on a logical key value.
  The following SELECT statement returns information from the table where the value of the LASTNAME column is greater than or equal to 'SMITH' and the value of the FIRSTNAME column is greater than 'JOHN':

  SELECT EMPNO, LASTNAME, HIREDATE
     FROM DSN8C10.EMP
     WHERE (LASTNAME, FIRSTNAME) >= ('SMITH', 'JOHN')
     ORDER BY HIREDATE ASC;

• For numeric based pagination: Use the OFFSET clause (either by itself, or with the FETCH clause) to skip a specified number of rows from the result set.
  To access part of Db2 result set based on an absolute position, the OFFSET clause can be specified as part of the SELECT statement. The OFFSET clause specifies the number of rows to skip from the beginning of a result set, which can be a more efficient way to filter unneeded rows. The OFFSET clause can be used with the FETCH clause to further limit the number of rows returned from the result set.
  The following SELECT statement skips the first 100 rows from the T1 table before it returns rows for the query:

  SELECT * FROM T1 
     OFFSET 100 ROWS;

  Using the OFFSET clause with the FETCH clause specifies the number of rows to skip from the beginning of the table before returning the number of rows specified in the FETCH clause:

  SELECT * FROM T1 
     OFFSET 10 ROWS
     FETCH FIRST 10 ROWS ONLY;

  To return three pages of 10 rows each, you might use statements similar to the following SQL statements:

  SELECT * FROM T1
     OFFSET 0 ROWS
     FETCH FIRST 10 ROWS ONLY;
  
  SELECT * FROM T1
     OFFSET 10 ROWS
     FETCH NEXT 10 ROWS ONLY;
  
  SELECT * FROM T1
     OFFSET 20 ROWS
     FETCH NEXT 10 ROWS ONLY;

  This example is three separate SQL statements, each with different values for the OFFSET clause. Each SELECT statement is processed as a new SQL statement.