Complete LIMIT clause reference: LIMIT n, LIMIT offset,row_count, LIMIT row_count OFFSET offset syntax, ORDER BY result handling, and UPDATE replication safety.
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:
LIMIT offset, row_count
LIMIT row_count OFFSET offsetWhen 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 UPDATE with the LIMIT clause is not safe for replication. LIMIT 0 is an exception to this rule.
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 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):
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
CREATE 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 |
+-------------------------------------------------------------+