You are right. There are some conditions that I have not considered...
A row is not the "oldest row", if it is the last inserted row of a group (its the current state of the group).
General: Do not delete rows with Ack=1 and do not delete the current state of a group (last inserted row of a group).
The question is that if there is the following pattern:
1 100 1
2 100 0
3 100 0
*To delete: ID=2 (is not the current state of the group)
After delete and insert:
*No one could be deleted, if the new inserted row has a different GroupID
And what about the following:
1 100 0
2 100 1
3 100 0
which row should be deleted, row 1 or row 3?
*To delete: ID=1 (is not the current state of the group)
After delete and insert:
*No one could be deleted, if the new inserted row has a different GroupID
And also for:
1 100 0
2 100 0
3 100 1
which row should be deleted, row 1 or row 2?
*To delete: ID=1 (is not the current state of the group)
After delete and insert:
*To delete: ID=2 (is not the current state of the group)
After delete and insert:
*No one could be deleted, if the new inserted row has a different GroupID
I think i solved it.
Based on the helpful implementations of Keith:
I added an exclude of the "current group state" rows.
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
Big THX to Keith!