How to Delete Top 'n' Rows in MySQL?

To demonstrate how to delete top 'n' rows in MySQL, let's suppose we have the following table:

+----+--------+
| id |  name  |
+----+--------+
|  1 | foo    |
|  2 | bar    |
|  3 | biz    |
|  4 | baz    |
|  5 | qux    |
|  6 | quux   |
|  7 | quuz   |
|  8 | corge  |
|  9 | grault |
| 10 | garply |
+----+--------+

CREATE TABLE customer (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(6) NOT NULL,
    PRIMARY KEY (id)
);

INSERT INTO customer(name)
VALUES ('foo'), ('bar'), ('biz'), ('baz'), ('qux'), ('quux'), ('quuz'), ('corge'), ('grault'), ('garply');

Deleting First 'n' Unordered Rows

We can use the LIMIT clause with DELETE to limit the number of rows we delete. For example, to delete the first five rows, we could do the following:

DELETE FROM customer LIMIT 5;

The query above is going to result in the following:

+----+--------+
| id |  name  |
+----+--------+
|  6 | quux   |
|  7 | quuz   |
|  8 | corge  |
|  9 | grault |
| 10 | garply |
+----+--------+

Deleting First 'n' Ordered Rows

To delete a set of ordered rows, we can simply use ORDER BY clause with DELETE like so:

DELETE FROM customer
ORDER BY name
LIMIT 5;

The above query will have the following result:

+----+--------+
| id |  name  |
+----+--------+
|  5 | qux    |
|  6 | quux   |
|  7 | quuz   |
|  9 | grault |
| 10 | garply |
+----+--------+