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.
LIMIT 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
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
Retrieve the record of the maximum order amount
To retrieve the record of the maximum order amount, you can sort the orders by amount in descending order 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
When 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 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
When 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
Pagination queries
Syntax of pagination SQL queries that include the LIMIT clause:
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, if page_no is set to 5 and page_size is set to 10, the 10 records on page 5 are retrieved.
Set the page_size parameter to 2 to retrieve data of page 1, page 2, and page 3, respectively, as shown in the following sample code:
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 & LIMIT
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;
Examples:
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
Usage notes
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