I insert rows into a table with time-stamps (time). I could have multiple rows that are identical except id and time. I want to delete all rows that are beyond 5 "iterations". I only want to keep 5 iterations of an instance's "history".
Instance = new entry for name
History = the entries for a particular name
Example:
id name value time
1 blue 15 12/1/2016
2 blue 16 12/2/2016
3 blue 12 12/3/2016
4 blue 43 12/4/2016
5 blue 12 12/5/2016
6 blue 9 12/6/2016
7 blue 33 12/7/2016
8 red 15 12/5/2016
9 red 15 12/8/2016
After Delete:
id name value time
3 blue 12 12/3/2016
4 blue 43 12/4/2016
5 blue 12 12/5/2016
6 blue 9 12/6/2016
7 blue 33 12/7/2016
8 red 15 12/5/2016
9 red 15 12/8/2016
Here was the adopted solution from the suggested almost-duplicate question:
delete l.* from table_name L inner join (
select name, group_concat(id order by time desc) grouped_value from table_name
) R on l.name=r.name and find_in_set(id,grouped_value) > 5