Delete rows based on multiple criteria

Question

I need to delete from my table (TABLE_X) rows that have multiple id_B but preserving the only one that have the greatest value of id_A. The image below could be more exhaustive. I want to delete only the highlighted rows.

Have you tried anything? Where are you stuck? – xQbert Oct 24 '19 at 16:16 — xQbert, Oct 24 '19 at 16:16

score 1 · Accepted Answer · answered Oct 24 '19 at 16:11

1

DELETE FROM TABLE_X
 WHERE id_A NOT IN (SELECT * 
                    FROM (SELECT MAX(n.id_A)
                            FROM TABLE_X n
                        GROUP BY n.id_B) x)

Note that the answer is taken from the following thread: Delete all Duplicate Rows except for One in MySQL?

answered Oct 24 '19 at 16:11

mustafaj

305
1
12

Thanks It works. This is what I am looking for. I had some problems with SELECT in DELETE operation – Tex'N'Duet Oct 25 '19 at 07:54

Delete rows based on multiple criteria

1 Answers1