MySQL: ลบแถวที่ n (Nth Row) ด้วย Subquery และ LIMIT

การลบแถวลำดับที่ N จากตารางด้วย Subquery และ LIMIT

MySQL ไม่มีคำสั่ง DELETE ... LIMIT 1 OFFSET n โดยตรง (รองรับในบาง Version แต่ไม่มาตรฐาน) วิธีมาตรฐานคือใช้ Subquery หา Primary Key ของแถวที่ N แล้วค่อย DELETE ด้วย PK นั้น

ตัวอย่างการใช้งาน

สร้างตารางและเพิ่มข้อมูล 6 แถว:

mysql> CREATE TABLE DemoTable1 (
    -> StudentId INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> StudentName VARCHAR(100)
    -> );
Query OK, 0 rows affected (0.99 sec)

mysql> INSERT INTO DemoTable1(StudentName) VALUES('Larry'),('Sam'),('Mike'),('Carol'),('David'),('Bob');
Query OK, 6 rows affected (0.12 sec)

ข้อมูลทั้งหมด:

mysql> SELECT * FROM DemoTable1;

+-----------+-------------+
| StudentId | StudentName |
+-----------+-------------+
| 1         | Larry       |
| 2         | Sam         |
| 3         | Mike        |
| 4         | Carol       |
| 5         | David       |
| 6         | Bob         |
+-----------+-------------+
6 rows in set (0.00 sec)

ลบแถวที่ 2 (N=2) → ใช้ OFFSET 1 (0-based) LIMIT 1:

mysql> DELETE FROM DemoTable1 
    -> WHERE StudentId = (
    ->     SELECT StudentId FROM (
    ->         SELECT StudentId FROM DemoTable1 ORDER BY StudentId LIMIT 1, 1
    ->     ) AS tbl
    -> );
Query OK, 1 row affected (0.19 sec)

ผลลัพธ์ (แถว Sam หายไป):

mysql> SELECT * FROM DemoTable1;

+-----------+-------------+
| StudentId | StudentName |
+-----------+-------------+
| 1         | Larry       |
| 3         | Mike        |
| 4         | Carol       |
| 5         | David       |
| 6         | Bob         |
+-----------+-------------+
5 rows in set (0.00 sec)

คำอธิบาย Subquery

1. SELECT StudentId FROM DemoTable1 ORDER BY StudentId LIMIT 1, 1 → หา ID ของแถวที่ 2 (Offset 1, Limit 1)
2. ห่อด้วย (SELECT ... ) AS tbl → MySQL ต้องการ Derived Table Alias
3. WHERE StudentId = (...) → ลบแถวที่มี ID ตรงกับผลลัพธ์

ข้อควรระวัง

• ต้องมี Primary Key หรือ Unique Key เพื่อระบุแถวได้ชัดเจน
• ORDER BY ใน Subquery สำคัญมาก ต้องกำหนดลำดับที่ชัดเจน
• MySQL 8.0.27+ รองรับ DELETE ... ORDER BY ... LIMIT offset, count โดยตรง (แต่ยังไม่ใช่ Standard SQL)

วิธีอื่น: ใช้ ROW_NUMBER() (MySQL 8.0+)

WITH ranked AS (
    SELECT StudentId, ROW_NUMBER() OVER (ORDER BY StudentId) AS rn
    FROM DemoTable1
)
DELETE FROM DemoTable1
WHERE StudentId = (SELECT StudentId FROM ranked WHERE rn = 2);

อ่านง่ายกว่าและรองรับเงื่อนไขซับซ้อนได้