LIMIT clause
- reference
The LIMIT clause specifies the maximum number of documents to be returned in a resultset by a SELECT statement.
Purpose
When you don’t need the entire resultset, use the LIMIT
clause to specify the maximum number of documents to be returned in a resultset by a SELECT
query.
The LIMIT
and OFFSET
clauses are evaluated after the ORDER BY
clause.
You can use the OFFSET
and LIMIT
clauses together to paginate the results — that is, to split the resultset into pages, each containing a specified number of documents, for display purposes.
Starting from version 4.5, the LIMIT clause in INSERT, UPDATE, and DELETE statements is no longer a hint. It indicates that the actual number of mutations will be less than or equal to the specified LIMIT. |
Arguments
- expr
-
Integer or an expression that evaluates to an integer representing the number of resulting documents. A negative value is the same as
LIMIT 0
.
Examples
SELECT name, address, city, country, url
FROM `travel-sample`.inventory.hotel
WHERE vacancy = true
LIMIT 2;
[
{
"address": "Capstone Road, ME7 3JE",
"city": "Medway",
"country": "United Kingdom",
"name": "Medway Youth Hostel",
"url": "http://www.yha.org.uk"
},
{
"address": "6 rue aux Juifs",
"city": "Giverny",
"country": "France",
"name": "The Robins",
"url": "http://givernyguesthouse.com/robin.htm"
}
]
The following query uses named parameters and expressions to display the specified page of results, assuming that page numbering starts at zero.
SELECT name, address, city, country, url
FROM `travel-sample`.inventory.hotel
WHERE vacancy = true
OFFSET $page * $results
LIMIT $results;
Setting the page number to zero, with two results per page, the results are the same as Example 1.
[
{
"address": "Capstone Road, ME7 3JE",
"city": "Medway",
"country": "United Kingdom",
"name": "Medway Youth Hostel",
"url": "http://www.yha.org.uk"
},
{
"address": "6 rue aux Juifs",
"city": "Giverny",
"country": "France",
"name": "The Robins",
"url": "http://givernyguesthouse.com/robin.htm"
}
]