patternsqlMinor

What is the SQL Standard method of doing LIMIT OFFSET?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

themethodwhatsqllimitoffsetdoingstandard

Problem

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?

Solution

SQL 2011 Spec

The syntax in the spec is defined as,

 ::=
  [  ] 
  [  ] [  ] [  ]

You can see here that ` and the

 are both optional and stand by themselves, moving on

 ::=
  OFFSET  { ROW | ROWS }

 ::=
  FETCH { FIRST | NEXT } [  ] { ROW | ROWS } { ONLY | WITH TIES }

 ::=
  
  | 

 ::=
  

 ::=
  

 ::=
   PERCENT

 ::=
  
  | 
  | 
  | 


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`

Code Snippets

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

<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>

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

Context

StackExchange Database Administrators Q#214367, answer score: 3

Revisions (0)

No revisions yet.