SQLite Forum

How to implement a table working like a ring buffer
Login
Or perhaps the following trigger:

```
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, ID, Ack);

create trigger t_bi_prune before insert on t
begin
  delete from t
   where ID = (
                 select ID
                   from t as o
                  where (select count(ID) from t where GroupID == o.GroupID and Ack != 1) > 1
                    and (select Ack from t where GroupID == o.GroupID and ID < o.ID order by ID desc limit 1) IS NOT 1
                    and Ack != 1
               order by ID
                  limit 1
              )
     and (
          select count(ID)
            from t
         ) >= 6;
end;
```

Which does the following:

for each ID in ascending order where Ack != 1  
  where there are more than 2 rows with Ack != 1 in the same GroupID  
    and the Ack of the previous row in the group is not 1  
when you have found 1 ID and there are 6 or more rows in t delete that ID

ie, the answers to the questions are 3 1 1 and for the following none would be deleted:

1 100 1  
2 100 0  
3 100 1

1 100 1  
2 100 0  
3 100 1  
4 100 0