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

Solution 1:

You can do this with a join:

delete r
    from reminder r left join
         (select name, max(memo) as maxmemo
          from reminder
          groupby name
         ) rn
         on r.name = rn.name and r.memo = rn.maxmemo
    where rn.name isnull;

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
          groupby name
         ) rn
         on r.name = rn.name and r.id = rn.maxid
    where rn.name isnull;