จัดเรียงและลบ N แถวใน MySQL ด้วย ORDER BY ... LIMIT

MySQL รองรับ ORDER BY ... LIMIT ในคำสั่ง DELETE โดยตรง

mysql> CREATE TABLE DemoTable (
    -> Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> FirstName VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.65 sec)

mysql> INSERT INTO DemoTable(FirstName) VALUES('Chris');
Query OK, 1 row affected (0.12 sec)
mysql> INSERT INTO DemoTable(FirstName) VALUES('Adam');
Query OK, 1 row affected (0.11 sec)
mysql> INSERT INTO DemoTable(FirstName) VALUES('John');
Query OK, 1 row affected (0.11 sec)
mysql> INSERT INTO DemoTable(FirstName) VALUES('David');
Query OK, 1 row affected (0.12 sec)
mysql> INSERT INTO DemoTable(FirstName) VALUES('Mike');
Query OK, 1 row affected (0.19 sec)
mysql> INSERT INTO DemoTable(FirstName) VALUES('Carol');
Query OK, 1 row affected (0.17 sec)
mysql> INSERT INTO DemoTable(FirstName) VALUES('Sam');
Query OK, 1 row affected (0.13 sec)

mysql> SELECT * FROM DemoTable;

+----+-----------+
| Id | FirstName |
+----+-----------+
|  1 | Chris     |
|  2 | Adam      |
|  3 | John      |
|  4 | David     |
|  5 | Mike      |
|  6 | Carol     |
|  7 | Sam       |
+----+-----------+
7 rows in set (0.00 sec)

ลบ 4 แถวแรกตามลำดับชื่อ A-Z:

mysql> DELETE FROM DemoTable ORDER BY FirstName ASC LIMIT 4;
Query OK, 4 rows affected (0.19 sec)

mysql> SELECT * FROM DemoTable;

+----+-----------+
| Id | FirstName |
+----+-----------+
|  3 | John      |
|  5 | Mike      |
|  7 | Sam       |
+----+-----------+
3 rows in set (0.00 sec)

⚠️ ระวัง: การ DELETE พร้อม ORDER BY LIMIT ไม่มี WHERE อาจลบข้อมูลผิด หากไม่แน่ใจ ให้ SELECT ก่อนตรวจสอบ