SQL$HELP_OLD72.HLB  —  Select Expressions, Arguments  OFFSET
    The OFFSET clause allows the database programmer to start
    fetching the result rows from the specified offset within the
    result table. OFFSET accepts a numeric value expression which may
    contain arbitrary arithmetic operators, function calls, subselect
    clauses or sequence references. The subselect clauses may not
    reference columns in the outer query as it is evaluated before
    row processing begins.

    The OFFSET clause is equivalent in functionality to the
    SKIP clause currently supported by the LIMIT TO clause. The
    distinction is that OFFSET can be specified without a row limit.

    This following query uses a subselect in the OFFSET clause to
    locate the median (or middle) row of the sorted set.

    SQL> select e.last_name, e.first_name, employee_id, sh.salary_amount
    cont> from salary_history sh inner join employees e using (employee_id)
    cont> where sh.salary_end is null
    cont> order by sh.salary_amount
    cont> offset (select count(*)
    cont>         from salary_history
    cont>         where salary_end is null)/2 rows
    cont> fetch next row only;
     E.LAST_NAME      E.FIRST_NAME   EMPLOYEE_ID   SH.SALARY_AMOUNT
     Boyd             Ann            00244               $24,166.00
    1 row selected
    SQL>
Close Help