You can use the LIMIT clause to limit the number of rows returned for a SELECT query. This feature is often used to specify the number of pages returned.
Syntax
Syntax of the SQL queries that include the LIMIT clause:
SELECT select_list FROM table_list LIMIT [offset,] count_num;
Parameters:
offset: the number of rows to be skipped. It is optional. The default value of theoffsetparameter is 0, which specifies to skip zero rows. Value range: [0,+∞).count_num: the number of rows of records to be retrieved after the rows specified by theoffsetparameter are skipped. Value range: [0,+∞).The values of the
offsetandcount_numparameters cannot be specified using expressions.
Queries that use LIMIT n, m
Retrieve the first m rows of records
Query the former five records of the order_id ID and fruit_price columns in the fruit_order table.
obclient> SELECT order_id ID, fruit_price Order amount FROM fruit_order LIMIT 5;
+----+--------------+
| ID | Order amount |
+----+--------------+
| 1 | 13.11 |
| 2 | 23.34 |
| 3 | 12.22 |
| 4 | 22.21 |
| 5 | 51.55 |
+----+--------------+
5 rows in set
obclient> SELECT order_id ID, fruit_price Order amount FROM fruit_order LIMIT 0,5;
+----+--------------+
| ID | Order amount |
+----+--------------+
| 1 | 13.11 |
| 2 | 23.34 |
| 3 | 12.22 |
| 4 | 22.21 |
| 5 | 51.55 |
+----+--------------+
5 rows in set
Obtain the m rows of records following the n skipped rows of records
Query three rows of records following the third row in the
fruit_ordertable.obclient> SELECT order_id ID, fruit_price Order amount FROM fruit_order LIMIT 3,3; +----+--------------+ | ID | Order amount | +----+--------------+ | 4 | 22.21 | | 5 | 51.55 | | 6 | 58.83 | +----+--------------+ 3 rows in setWhen the remaining data is less than m rows after you skip n rows of records, the query result is all the remaining data.
obclient> SELECT order_id ID, fruit_price Order amount FROM fruit_order; +----+--------------+ | ID | Order amount | +----+--------------+ | 1 | 13.11 | | 2 | 23.34 | | 3 | 12.22 | | 4 | 22.21 | | 5 | 51.55 | | 6 | 58.83 | | 7 | 14.66 | | 8 | 34.44 | | 9 | 63.66 | +----+--------------+ 9 rows in set obclient> SELECT order_id ID, fruit_price Order amount FROM fruit_order LIMIT 3,7; +----+--------------+ | ID | Order amount | +----+--------------+ | 4 | 22.21 | | 5 | 51.55 | | 6 | 58.83 | | 7 | 14.66 | | 8 | 34.44 | | 9 | 63.66 | +----+--------------+ 6 rows in set
Retrieve the record of the maximum order amount
To retrieve the record of the maximum order amount, you can sort the orders in descending order of amount and then retrieve the first record.
obclient> SELECT order_id ID, fruit_price Order amount FROM fruit_order ORDER BY fruit_price DESC LIMIT 1;
+----+--------------+
| ID | Order amount |
+----+--------------+
| 9 | 63.66 |
+----+--------------+
1 row in set
Pagination queries
The LIMIT clause can be used based on the logic of pagination queries.
SQL statement:
SELECT select_list FROM table_list LIMIT (page_no - 1) * page_size, page_size;
Parameters in the syntax:
page_no: the page number that starts from 1. Value range: [1,+∞).page_size: the number of records per page. Value range: [1,+∞). For example, ifpage_nois set to5andpage_sizeis set to10, the 10 records on page 5 are retrieved.
Example:
Set the page_size parameter to 2 to retrieve data of page 1, page 2, and page 3.
obclient> SELECT order_id ID, fruit_price Order amount FROM fruit_order ORDER BY fruit_price DESC;
+----+--------------+
| ID | Order amount |
+----+--------------+
| 9 | 63.66 |
| 6 | 58.83 |
| 5 | 51.55 |
| 8 | 34.44 |
| 2 | 23.34 |
| 4 | 22.21 |
| 7 | 14.66 |
| 1 | 13.11 |
| 3 | 12.22 |
+----+--------------+
9 rows in set
obclient> SELECT order_id ID, fruit_price Order amount FROM fruit_order ORDER BY fruit_price DESC LIMIT 0,2;
+----+--------------+
| ID | Order amount |
+----+--------------+
| 9 | 63.66 |
| 6 | 58.83 |
+----+--------------+
2 rows in set
obclient> SELECT order_id ID, fruit_price Order amount FROM fruit_order ORDER BY fruit_price DESC LIMIT 2,2;
+----+--------------+
| ID | Order amount |
+----+--------------+
| 5 | 51.55 |
| 8 | 34.44 |
+----+--------------+
2 rows in set
obclient> SELECT order_id ID, fruit_price Order amount FROM fruit_order ORDER BY fruit_price DESC LIMIT 4,2;
+----+--------------+
| ID | Order amount |
+----+--------------+
| 2 | 23.34 |
| 4 | 22.21 |
+----+--------------+
2 rows in set
Use WHERE, GROUP BY, HAVING, ORDER BY, and LIMIT in a query
If you use the WHERE, GROUP BY, HAVING, ORDER BY, and LIMIT keywords in a query, make sure that you follow the sequence specified in the following syntax:
SELECT select_list FROM table_name
WHERE query_condition
GROUP BY group_by_expression
HAVING group_condition
ORDER BY column_list][ASC | DESC]
LIMIT [offset,] count_num;
Example:
obclient> SELECT user_id Customer ID, COUNT(order_id) Number of orders FROM fruit_order t
WHERE t.order_year = 2021
GROUP BY user_id
HAVING count(order_id)>=2
ORDER BY Number of orders DESC
LIMIT 2;
+------------+-----------------+
| Customer ID| Number of orders|
+------------+-----------------+
| 1022 | 2 |
+------------+-----------------+
1 row in set
Considerations
LIMITdoes not support expressions and can be followed only by numbers.obclient> SELECT order_id ID, fruit_price Order amount FROM fruit_order LIMIT 3+1,7; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near '(3+1),7' at line 1LIMITcannot be followed by negative numbers.obclient> SELECT order_id ID, fruit_price Order amount FROM fruit_order LIMIT -3; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near '-3' at line 1