Details
-
Bug
-
Status: Closed
-
Major
-
Resolution: Fixed
-
None
-
None
-
None
Description
SQL:2008 introduced OFFSET and FETCH; LIMIT is a similar clause to do the same thing.
Postgres supports both; MySQL just LIMIT; SQL Server supports OFFSET/FETCH.
The rest of this description is from PostgreSQL's site http://www.postgresql.org/docs/8.4/static/sql-select.html#SQL-LIMIT
SELECT ...
[ ORDER BY expression ]
[ LIMIT
[ OFFSET start [ ROW | ROWS ] ]
[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
The LIMIT clause consists of two independent sub-clauses:
LIMIT { count | ALL }
OFFSET start
count specifies the maximum number of rows to return, while start specifies the number of rows to skip before starting to return rows. When both are specified, start rows are skipped before starting to count the count rows to be returned.
If the count expression evaluates to NULL, it is treated as LIMIT ALL, i.e., no limit. If start evaluates to NULL, it is treated the same as OFFSET 0.
SQL:2008 introduced a different syntax to achieve the same thing, which PostgreSQL also supports. It is:
OFFSET start
{ ROW | ROWS }FETCH { FIRST | NEXT } [ count ] { ROW | ROWS }
ONLY
Both clauses are optional, but if present the OFFSET clause must come before the FETCH clause. ROW and ROWS as well as FIRST and NEXT are noise words that don't influence the effects of these clauses. In this syntax, when using expressions other than simple constants for start or count, parentheses will be necessary in most cases. If count is omitted in FETCH, it defaults to 1.
---------------- Imported from GitHub ----------------
Url: https://github.com/julianhyde/optiq/issues/43
Created by: julianhyde
Labels:
Created at: Fri Jun 07 23:43:59 CEST 2013
State: closed
Attachments
Issue Links
- is related to
-
CALCITE-1886 Support "LIMIT [offset,] row_count", per MySQL
-
- Closed
-