USING 'OFFSET N ROWS' CLAUSE IN SELECT STATEMENT

Answer

The 'FETCH FIRST N ROWS' clause allows you to restrict the data
set from a 'SELECT' to only N rows. However the count starts
from the first row in the data set. With the new clause
'OFFSET N ROWS' you can now specify the count to start from row N.

For example imagine a table like 'orders' below:

    create table orders (order_dt timestamp, name char(20))

    create index idx1 on orders(order_dt)

    '2016-03-08 10:33:09', 'Pierre'

    '2016-04-18 05:17:40', 'John'

    '2016-04-28 20:50:31', 'Seongyeon'

    '2016-02-28 10:50:31', 'Kelly'

    '2016-05-12 10:40:38', 'Nadine'

    '2016-06-15 01:20:01', 'Mina'

    '2016-07-06 12:30:34', 'Serge'

    '2016-01-02 20:03:22', 'Pedro'

    '2016-01-08 21:53:41', 'Nestor'

    '2016-03-27 00:40:51', 'Phil'

    '2016-04-09 11:45:36', 'Georges'

    '2016-06-21 22:52:21', 'Natalie'

    '2016-07-08 04:10:35', 'Frederic'

    select order_dt, name

    from orders

    order by order_dt

    offset 3 rows fetch first 3 rows only

    ORDER_DT                   NAME

    -------------------------- --------------------

    2016-03-08-10.33.09.000000 Pierre

    2016-03-27-00.40.51.000000 Phil

    2016-04-09-11.45.36.000000 Georges



      3 record(s) selected.