My solution works, but its really slow.
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 ) > 1
and Ack != 1
except
select max(ID) from t group by GroupID LIMIT 1);
end
4000 rows - insert one row : needs more than 1.3 sec
I'm using following index
create unique index t_GroupID on t (GroupID, ID);
The query planer shows: the index is used.
I think max() with "group by" eats a lot of time.
Is there any way to speed this up?