If you are always providing the ID (that is do not depend on it being the RowID), then the following definition will be slightly more efficient:
```
create table t
(
ID integer primary key,
GroupID integer not null,
Ack integer not null check (Ack in (0,1))
) without rowid;
create unique index t_Deleteable_ID on t (ID, GroupID) where Ack != 1;
create unique index t_GroupID on t (GroupID, ID);
```
Basically it works because the index t_deleteable_id is an index containing all the IDs which may be deleted together with "covering" the GroupID so no access to the underlying table is required. The "select ID from t where ack != 1 order by ID" simply goes through this index only and then for each eligible ID uses the GroupID from the index to "select count(ID) from t" for that GroupID and eliminates if the group does not have more than one ID. The process stops looking when the first passing ID is found (limit 1).
You could speed up the whole thing (again at the expense of disk space) if you kept track of all the information you need to manage the lists with other triggers and tables. For example:
```
create table t
(
ID integer primary key,
GroupID integer not null,
Ack integer not null check (Ack in (0,1))
) without rowid;
create table t_group_count
(
GroupID integer not null primary key,
Count integer not null
) without rowid;
create table t_count
(
Rows integer not null default 1
);
insert into t_count values (0);
create unique index t_Deleteable_ID on t (ID, GroupID) where Ack != 1;
create trigger t_insert_count after insert on t
begin
insert into t_group_count values (new.GroupID, 1) on conflict (GroupID) do update set Count = Count + excluded.Count;
update t_count set Rows = Rows + 1;
end;
create trigger t_delete_count after delete on t
begin
insert into t_group_count values (old.GroupID, 1) on conflict (GroupID) do update set Count = Count - excluded.Count;
update t_count set Rows = Rows - 1;
end;
create trigger t_bi_prune before insert on t
begin
delete from t
where ID = (
select ID
from t as o
where (select Count from t_group_count where GroupID == o.GroupID) > 1
and Ack != 1
order by ID
limit 1
)
and (
select Rows
from t_count
) >= 6;
end;
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);
```
Which uses a couple of additional tables so that the counts are updated at insert/delete time eliminating the overhead of counting all the rows in the group or the table t ...