SQLite Forum

How to implement a table working like a ring buffer
It takes a long time because you are using EXCEPT.  This means that the select preceding the EXCEPT must be executed in its entirety and the select after the except must be run in its entirety, the EXCEPT applied, and then the LIMIT 1 is applied.

If you want to exclude the max(ID) in each group then you need to execute the check for each candidate as it arises so that you can stop looking as soon as a valid ID is found rather than processing the entire query then only using the first result.

The difference is between examining the entire deck of cards to find the ones eligible for disposal and then using the first one in the resulting list compared to determining which one to discard by looking at each in turn and stopping (and discarding) as soon as you find the first one to discard.

create trigger t_bi_prune before insert on t
  delete from t
    where ID = (
                   select ID
                     from t as o
                    where (select count(ID) from t where GroupID  == o.GroupID ) > 1
                      and (select ID from t where GroupID == o.GroupID and ID > o.ID order by ID limit 1) IS NOT NULL
                      and Ack != 1
                 order by ID
                    LIMIT 1