Search code examples
mysqlsubquerysql-delete

Deleting from table using subquery

I have for example this table "autos"

id | name
--------------
2  | Mercedes
5  | Mercedes
6  | Mercedes
7  | BMW 
9  | BMW

I want delete from this table all rows, except rows, where id is maximal for each auto. that is, I want after deleting, in table remains only this rows:

6 | Mercedes
9 | BMW

I write this query, but this not working (and not returns errors also).

DELETE FROM autos WHERE id NOT IN (
    SELECT id FROM (
        SELECT MAX(id) FROM autos GROUP BY name
    ) AS t
)

please tell me, how to make this?

Solution

  • AFAIK, the following should work:

    DELETE FROM autos WHERE id NOT IN (
    SELECT max_id FROM (
        SELECT MAX(id) as max_id, name FROM autos GROUP BY name
    ) as t
)