SQLite Forum

How to implement a table working like a ring buffer
> ID is the lowest of all possible "oldest row" candidates which results from grouping by GroupId

does not make any sense.  If it is actually relevant please explain.

So to delete only the one minimum row that is not excluded by the above when there will be than 6 rows in the table and preferring performance over disk usage, the following would work:

create table t
    ID      integer primary key,
    GroupID integer not null,
    Ack     integer not null check (Ack in (0,1))
create unique index t_Deleteable_ID on t (ID, GroupID) where Ack != 1;
create index t_GroupID on t (GroupID);

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 Ack != 1
               order by ID
                  limit 1
     and (
          select count(ID)
            from t
         ) >= 6;

insert into t values (1, 100, 0), (2, 200, 1), (3, 200, 0), (4, 300, 0), (5, 300, 0), (6, 200, 0), (7, 200, 0);

from which you can see that the insertion of the last item resulted in the deletion of ID == 3.

Whether further performance optimization is required depends on the shape of your data.