MySQL: ลบแถวซ้ำและเก็บเฉพาะแถวที่มีค่าสูงสุด (MAX) ต่อกลุ่ม

สถานการณ์

ตารางมีข้อมูลซ้ำทั้งแถวเต็มรูปแบบ (duplicate rows) และข้อมูลที่ Key (Id, Name) ซ้ำกันแต่ค่า Age ต่างกัน ต้องการเก็บแค่ 1 แถวต่อกลุ่ม (Id, Name) โดยเลือกแถวที่ Age สูงสุด

ข้อมูลตัวอย่าง

mysql> CREATE TABLE DemoTable1468 (
    Id INT,
    Name VARCHAR(20),
    Age INT
);

INSERT INTO DemoTable1468 VALUES (100, 'Chris', 23);
INSERT INTO DemoTable1468 VALUES (101, 'Bob', 25);
INSERT INTO DemoTable1468 VALUES (102, 'David', 30);
INSERT INTO DemoTable1468 VALUES (100, 'Chris', 23);  -- duplicate ทุกคอลัมน์
INSERT INTO DemoTable1468 VALUES (100, 'Chris', 38);  -- Id,Name ซ้ำ Age ต่าง
INSERT INTO DemoTable1468 VALUES (101, 'Bob', 23);    -- Id,Name ซ้ำ Age ต่าง

ข้อมูลทั้งหมด 6 แถว

+------+-------+------+
| Id   | Name  | Age  |
+------+-------+------+
| 100  | Chris | 23   |
| 101  | Bob   | 25   |
| 102  | David | 30   |
| 100  | Chris | 23   |
| 100  | Chris | 38   |
| 101  | Bob   | 23   |
+------+-------+------+

Query ดึงข้อมูลที่ต้องการ (SELECT)

mysql> SELECT Id, Name, MAX(Age) AS Age
    -> FROM DemoTable1468
    -> GROUP BY Id, Name;

ผลลัพธ์ 3 แถว

+------+-------+------+
| Id   | Name  | Age  |
+------+-------+------+
| 100  | Chris | 38   |
| 101  | Bob   | 25   |
| 102  | David | 30   |
+------+-------+------+

หากต้องการลบแถวซ้ำจริงๆ (DELETE)

MySQL ไม่รองรับ JOIN ใน DELETE โดยตรงกับตารางเดียวกัน วิธีที่ปลอดภัยคือสร้างตารางใหม่:

CREATE TABLE DemoTable1468_Clean AS
SELECT Id, Name, MAX(Age) AS Age
FROM DemoTable1468
GROUP BY Id, Name;

-- เพิ่ม Primary Key / Index ตามต้องการ
ALTER TABLE DemoTable1468_Clean ADD PRIMARY KEY (Id, Name);

-- แทนที่ตารางเดิม
RENAME TABLE DemoTable1468 TO DemoTable1468_Old,
             DemoTable1468_Clean TO DemoTable1468;

วิธีทางเลือก: ใช้ ROW_NUMBER() (MySQL 8.0+)

WITH Ranked AS (
    SELECT Id, Name, Age,
           ROW_NUMBER() OVER (PARTITION BY Id, Name ORDER BY Age DESC) AS rn
    FROM DemoTable1468
)
SELECT Id, Name, Age FROM Ranked WHERE rn = 1;

วิธีนี้ยืดหยุ่นกว่า เลือกเกณฑ์อื่นได้ (เช่นเก็บแถวล่าสุดด้วย timestamp)

สรุป

• GROUP BY Id, Name + MAX(Age) ใช้ได้ง่าย สำหรับดูผลลัพธ์
• ต้องการลบจริง ให้สร้างตารางใหม่จากผล SELECT แล้ว RENAME
• MySQL 8.0+ ควรใช้ Window Function ROW_NUMBER() ควบคุมรายละเอียดได้มากกว่า