SQLite Forum

How to implement a table working like a ring buffer
On subsequent examination one can only conclude that the requirement for the entry to be deleted is "not the last one" implies that there exists more than one entry in the group and hence the two correlated subqueries are redundant and can be optimized by getting rid of the useless check for more than one member of the group.

create trigger t_bi_prune before insert on t
  delete from t
    where ID = (
                   select ID
                     from t as o
                    where exists (select * from t where GroupID == o.GroupID and ID > o.ID order by ID)
                      and Ack != 1
                 order by ID
                    limit 1

This should make the trigger execute even faster.