What is the SQL Standard method of doing LIMIT OFFSET?

Question

I know LIMIT and OFFSET are pretty common but unstandardized methods to page through a result set. I know there is a standardized method, but I can never find this when I'm going to look it up,

• The PostgreSQL LIMIT/OFFSET docs don't link to it,
• The PostgreSQL FETCH docs don't link to it either.

I know it's not either of them. What is the SQL Standard and PostgreSQL syntax for LIMIT and OFFSET?

Answer 1

SQL 2011 Spec

The syntax in the spec is defined as,

<query expression> ::=
  [ <with clause> ] <query expression body>
  [ <order by clause> ] [ <result offset clause> ] [ <fetch first clause> ]

You can see here that <result offset clause> and the <fetch first clause> are both optional and stand by themselves, moving on

<result offset clause> ::=
  OFFSET <offset row count> { ROW | ROWS }

<fetch first clause> ::=
  FETCH { FIRST | NEXT } [ <fetch first quantity> ] { ROW | ROWS } { ONLY | WITH TIES }

<fetch first quantity> ::=
  <fetch first row count>
  | <fetch first percentage>

<offset row count> ::=
  <simple value specification>

<fetch first row count> ::=
  <simple value specification>

<fetch first percentage> ::=
  <simple value specification> PERCENT

<simple value specification> ::=
  <literal>
  | <host parameter name>
  | <SQL parameter reference>
  | <embedded variable name>

RDBMS-Specific

PostgreSQL

The SQL:2011 Spec supports a bit more than PostgreSQL, namely a WITH TIES option (F867) and the ability to specify PERCENT option (F866). The query is defined as,

PostgreSQL only documents this, AFAIK, in SELECT,

SELECT
...
[ OFFSET start [ ROW | ROWS ] ]
[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]

For more information aside from PostgreSQL, and the spec see the tag-info for offset-fetch