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 as ROW/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 records
  • OFFSET 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 ( /* ... */ )