LIMIT Clause
The LIMIT
clause specifies a limit on the number of records returned from the SELECT
command. An optional offset (the number of rows to skip) can be specified. The LIMIT
clause can also be specified using the SQL 2008 OFFSET
/FETCH FIRST
clauses. If an ORDER BY
is also specified, it will be applied before OFFSET
/LIMIT
are applied. If an ORDER BY
is not specified there is generally no guarantee what subset of rows will be returned.
Usage
LIMIT [offset,] limit
-- or
[OFFSET offset ROW|ROWS] [FETCH FIRST|NEXT [limit] ROW|ROWS ONLY
Syntax Rules
- The
limit
/offset
expressions must be a non-negative integer. An offset of 0 is ignored. A limit of 0 will return no rows; - The terms
FIRST
/NEXT
are interchangeable, as well asROW
/ROWS
; - The limit clause may take an optional preceding
NON_STRICT
hint to indicate that push operations should not be inhibited even if the results will not be consistent with the logical application of the limit. The hint is only needed on unordered limits, e.g.SELECT * FROM VW /*+ NON_STRICT */ LIMIT 2
. Examples:LIMIT 100
- returns the first 100 records (rows 1-100)LIMIT 500, 100
- skips 500 records and returns the next 100 records (rows 501-600)OFFSET 500 ROWS
- skips 500 recordsOFFSET 500 ROWS FETCH NEXT 100 ROWS ONLY
- skips 500 records and returns the next 100 records (rows 501-600)FETCH FIRST ROW ONLY
- returns only the first record
- Be sure that there is no whitespace between
/*
and+
when using e.g./*+ NON_STRICT */
; - Configure your SQL client not to remove multi-line comments (in Squirrel: Session->Session Properties->SQL-> Remove multi-line comment ( /* ... */ )