LIMIT TO limit-expression
       LIMIT TO skip-expression

    The LIMIT TO clause allows you to limit the number of rows
    in the result table, or to skip rows returned from a query.
    For example, the first row in the result set might be the
    column headings loaded from a CSV data source loaded by the
    RMU/LOAD/RECORD=FORMAT=DELIMITED command that should be ignored
    by queries.

    If either limit-expression or skip-expression is specified as a
    numeric literal, then it must be an unscaled value. These numeric
    expressions are converted to BIGINT before executing the query.

    Neither limit-expression nor skip-expression can reference
    columns from the select-expression in which they occur. You
    can use only columns of a subselect specified for the limit-
    expression or skip-expression. The example in this section uses a
    subselect in the LIMIT TO clause.

                                   NOTE

       Oracle recommends that the values specified for skip-
       expression be kept small for performance reasons. The
       skipped rows are still fetched and processed by the query;
       they are just not returned to the application.

    If limit-expression is evaluated to a negative value or zero,
    then no rows are returned from the query, and no error is
    reported.

    If skip-expression is evaluated to a negative value or zero, then
    no rows are skipped. If the skip-expression is larger than the
    rows in the result set, then no rows are returned from the query,
    and no error is reported. The following examples show the use of
    the LIMIT TO ... SKIP syntax.

    This query returns the 100th employee from the EMPLOYEES table:

    SQL> select last_name, first_name, employee_id
    cont> from employees
    cont> order by employee_id
    cont> limit to 1 skip 99 rows;
     LAST_NAME        FIRST_NAME   EMPLOYEE_ID
     Herbener         James        00471
    1 row selected

    To retrieve the last row in the sorted list, you can replace the
    literal value with a subselect that calculates the value as shown
    in the following example. This query also shows the output from
    the SET FLAGS command for the query strategy.

    SQL> set flags 'strategy,detail';
    SQL> select last_name, first_name, employee_id
    cont> from employees
    cont> order by employee_id
    cont> limit to 1
    cont> skip (select count(*)-1 from employees) rows;
    Tables:
      0 = EMPLOYEES
      1 = EMPLOYEES
    Cross block of 2 entries
      Cross block entry 1
        Aggregate: 0:COUNT (*)
        Index only retrieval of relation 1:EMPLOYEES
          Index name  EMP_EMPLOYEE_ID [0:0]
      Cross block entry 2
        Firstn: 1
        Skipn: <agg0> - 1
        Get     Retrieval by index of relation 0:EMPLOYEES
          Index name  EMP_EMPLOYEE_ID [0:0]
     LAST_NAME        FIRST_NAME   EMPLOYEE_ID
     Herbener         James        00471
    1 row selected
    SQL>

    An alternative to this query would be to use ORDER ... DESC and
    then to use a LIMIT 1 ROW clause.

    This query finds the statistical median salary:

    SQL> -- select the median salary
    SQL> select salary_amount
    cont> from salary_history
    cont> where salary_end is NULL
    cont> order by salary_amount
    cont> limit to 1
    cont> skip (select count(*)/2
    cont>       from salary_history
    cont>       where salary_end is NULL);
     SALARY_AMOUNT
        $24,166.00
    1 row selected
    SQL>

    This result can be compared with the average salary:

    SQL> -- select the median salary compare with average
    SQL> select salary_amount as median_salary,
    cont>        (select avg (salary_amount)
    cont>         from salary_history
    cont>         where salary_end is NULL) as avg_salary edit using SALARY
    cont> from salary_history
    cont> where salary_end is NULL
    cont> order by salary_amount
    cont> limit to 1
    cont> skip (select count(*)/2
    cont>       from salary_history
    cont>       where salary_end is NULL);
     MEDIAN_SALARY    AVG_SALARY
        $24,166.00    $31,922.79
    1 row selected
    SQL>