CODEWITHSUNDEEP
mysqlsqlmaxdelete-row
Fanch
Fanch

Reputation: 3274

sql, keep only max value and delete others

I have this sort of table :

-id | name | memo

-1 | Gotham | s1ep1

-2 | Gotham | s1ep3

-3 | Gotham | s1ep5

I would like to keep the entry with the max(memo) and delete others, so just keep the third one (ep5).

I can retrieve the result of all max(memo) group by name like this :

    SELECT id,max(memo) FROM `reminder` group by name

But I don't find the proper way to delete others, even looking at similar topics.

I expected something like "delete every entries that are not in my selection".

    delete from reminder where not exists (SELECT id,max(memo) FROM `reminder` group by name)

But it doesn't work, "You can't specify target table 'reminder' for update in FROM clause". I must do it badly. Thanks for help.

Upvotes: 0

Views: 2165

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You can do this with a join:

delete r
    from reminder r left join
         (select name, max(memo) as maxmemo
          from reminder
          group by name
         ) rn
         on r.name = rn.name and r.memo = rn.maxmemo
    where rn.name is null;

As an aside. More typically, one wants to keep the row with the highest id. The structure is the same, just the columns are different:

delete r
    from reminder r left join
         (select name, max(id) as maxid
          from reminder
          group by name
         ) rn
         on r.name = rn.name and r.id = rn.maxid
    where rn.name is null;

Upvotes: 1

Related Questions