Paging statement

ORDER BY, OFFSET and LIMIT clauses can be used together to divide the result dataset into discrete subsets or pages. Such processes are often known as paging.

Combine these clauses to page through results. They can also be used with other statements. For more information, see ORDER BY clause, OFFSET clause, and LIMIT clause.

Syntax

diagramsource

paging_statement

paging_statement ::=
    order_by_clause offset_clause? limit_clause?
  | offset_clause limit_clause?
  | limit_clause

Parameters

order_by_clause

Sorts the rows in the results in ascending or descending order. For more information, see ORDER BY clause.

offset_clause

Skips the specified number of rows in the results before returning succeeding rows. For more information, see OFFSET clause.

limit_clause

Specifies the upper bound of the number of rows to be returned. For more information, see LIMIT clause.

Examples

Paging with a FOR statement

'''
FOR i IN LIST[3, 2, 5, 4, 1] 
ORDER BY i ASC 
OFFSET 3 
RETURN i
'''

The preceding statement sorts the numbers in a list in ascending order, then skips the first three records and returns the remaining records starting with the fourth.

Return:

+---+
| i |
+---+
| 4 |
| 5 |
+---+

Paging before a return statement

'''
FOR i IN LIST[3, 2, 5, 4, 1] 
ORDER BY i ASC 
OFFSET 1 
LIMIT 3 
RETURN i
'''

The preceding statement performs sorting and paging operations before the RETURN statement.

Return:

+---+
| i |
+---+
| 2 |
| 3 |
| 4 |
+---+

If the clauses do not follow the order defined in the syntax, they are treated as multiple paging statements, each of which contains only one clause. Refer to the following example.

'''
FOR i IN LIST[3, 2, 5, 4, 1] 
LIMIT 3 
OFFSET 1 
RETURN i
'''

The preceding statement executes two paging statements before the RETURN statement. The first is a page statement with a LIMIT clause and the second is a page statement with an OFFSET clause.

This is because according to the syntax, the OFFSET clause can only be followed by the LIMIT clause. Now that the clauses do not follow the order defined in the syntax, they are treated as two consecutive page statements.

Return:

+---+
| i |
+---+
| 2 |
| 5 |
+---+

You can execute the following statement to view the execution plan.

'''
EXPLAIN 
FOR i IN LIST[3, 2, 5, 4, 1] 
LIMIT 3 
OFFSET 1 
RETURN i
'''

Return:

---------------+---------------------------------------------------------
| planNodes    | details                                                |
---------------+---------------------------------------------------------
| Limit        | Offset 1, Limit 18446744073709551615, IS Partial false |
| └─Limit      | Offset 0, Limit 3, IS Partial false                    |
|   └─Unwind   | For i IN [3,2,5,4,1]                                   |
|     └─Values | UNIT                                                   |
---------------+---------------------------------------------------------

Paging after a return statement

A RETURN statement can be followed by only one paging statement. For more information, see Linear query statement.

'''
FOR i IN LIST[3, 2, 5, 4, 1] 
RETURN i 
OFFSET 1 
LIMIT 3
'''

Return:

+---+
| i |
+---+
| 2 |
| 5 |
| 4 |
+---+