Restrict the number of rows returned. This clause specifies the maximum number of records to return and can optionally set an offset.
Use the LIMIT clause to restrict the number of returned rows. When you use a single integer n with LIMIT, the first n rows will be returned. Use the ORDER BY clause to control which rows come first. You can also select a number of rows after an offset using either of the following:
When you provide an offset m with a limit n, the first m rows will be ignored, and the following n rows will be returned.
Executing an with the LIMIT clause is not safe for replication. LIMIT 0 is an exception to this rule (see ).
There is a optimization which provides the means to terminate the execution of statements which examine too many rows, and thus use too many resources. See .
It is possible to use LIMIT (or ) in a multi-table statement.
It is not possible to use LIMIT (or ) in a multi-table statement.
It is possible to use LIMIT with .
It is not possible to use LIMIT with .
Select the first two names (no ordering specified):
All the names in alphabetical order:
The first two names, ordered alphabetically:
The third name, ordered alphabetically (the first name would be offset zero, so the third is offset two):
From , LIMIT can be used in a multi-table update:
When using LIMIT with , you can simplify certain queries. Consider this table:
The following query works fine, but is rather complex:
It can be simplified to this:
Like limit, but also support WITH TIES
This page is licensed: CC BY-SA / Gnu FDL
LIMIT offset, row_count
LIMIT row_count OFFSET offsetCREATE TABLE members (name VARCHAR(20));
INSERT INTO members VALUES('Jagdish'),('Kenny'),('Rokurou'),('Immaculada');
SELECT * FROM members;
+------------+
| name |
+------------+
| Jagdish |
| Kenny |
| Rokurou |
| Immaculada |
+------------+SELECT * FROM members LIMIT 2;
+---------+
| name |
+---------+
| Jagdish |
| Kenny |
+---------+SELECT * FROM members ORDER BY name;
+------------+
| name |
+------------+
| Immaculada |
| Jagdish |
| Kenny |
| Rokurou |
+------------+SELECT * FROM members ORDER BY name LIMIT 2;
+------------+
| name |
+------------+
| Immaculada |
| Jagdish |
+------------+SELECT * FROM members ORDER BY name LIMIT 2,1;
+-------+
| name |
+-------+
| Kenny |
+-------+CREATE TABLE warehouse (product_id INT, qty INT);
INSERT INTO warehouse VALUES (1,100),(2,100),(3,100),(4,100);
CREATE TABLE store (product_id INT, qty INT);
INSERT INTO store VALUES (1,5),(2,5),(3,5),(4,5);
UPDATE warehouse,store SET warehouse.qty = warehouse.qty-2, store.qty = store.qty+2
WHERE (warehouse.product_id = store.product_id AND store.product_id >= 1)
ORDER BY store.product_id DESC LIMIT 2;
SELECT * FROM warehouse;
+------------+------+
| product_id | qty |
+------------+------+
| 1 | 100 |
| 2 | 100 |
| 3 | 98 |
| 4 | 98 |
+------------+------+
SELECT * FROM store;
+------------+------+
| product_id | qty |
+------------+------+
| 1 | 5 |
| 2 | 5 |
| 3 | 7 |
| 4 | 7 |
+------------+------+CREATE TABLE d (dd DATE, cc INT);
INSERT INTO d VALUES ('2017-01-01',1);
INSERT INTO d VALUES ('2017-01-02',2);
INSERT INTO d VALUES ('2017-01-04',3);SELECT SUBSTRING_INDEX(GROUP_CONCAT(CONCAT_WS(":",dd,cc)
ORDER BY cc DESC),",",1) FROM d;
+----------------------------------------------------------------------------+
| SUBSTRING_INDEX(GROUP_CONCAT(CONCAT_WS(":",dd,cc) ORDER BY cc DESC),",",1) |
+----------------------------------------------------------------------------+
| 2017-01-04:3 |
+----------------------------------------------------------------------------+SELECT GROUP_CONCAT(CONCAT_WS(":",dd,cc)
ORDER BY cc DESC LIMIT 1) FROM d;
+-------------------------------------------------------------+
| GROUP_CONCAT(CONCAT_WS(":",dd,cc) ORDER BY cc DESC LIMIT 1) |
+-------------------------------------------------------------+
| 2017-01-04:3 |
+-------------------------------------------------------------+