-2

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
Tom
  • 917
  • 2
  • 12
  • 23
  • Five rows based on just the `name` column? – Gurwinder Singh Dec 28 '16 at 20:13
  • 1
    Don't throw jargon at us. What's an "iteration"? What's an "instance"? What's a "history"? These terms are meaningless in the context of your tables, and you didn't bother to explain them. – shmosel Dec 28 '16 at 20:14
  • Easy, @shmosel. I was trying to explain it with words as well. If I was throwing jargon, I wouldn't use quotation marks. I'm assuming you know the basic meaning of the words "iteration" and "history". – Tom Dec 28 '16 at 20:17
  • Also, five rows based on what? time? decreasing id? – Gurwinder Singh Dec 28 '16 at 20:17
  • @tom On the contrary, quotations means you're using jargon and you know it. Of course I know the basic meaning, but it's unclear what they mean in this context without making assumptions about the nature of your data. – shmosel Dec 28 '16 at 20:18
  • @GurwinderSingh For this example, yes, on name. Based one time, yes. So sorted in descending order by time, keep top 5, per say. – Tom Dec 28 '16 at 20:19
  • DELETE with HAVING and COUNT() if more than 5 with a WHERE clause ought to do it. – Funk Forty Niner Dec 28 '16 at 20:24
  • @tom I did not mean that in a belligerent way, and I'm sorry if you took it that way. I was just pointing out that the use of quotations indicates that you realize (at some level) you're using the words outside of their natural meaning. – shmosel Dec 28 '16 at 20:25
  • @shmosel I tried updating my question to explain further. Your duplicate suggestion might be the solution. Thanks for the help. – Tom Dec 28 '16 at 20:26
  • so what do you want to, delete or select and just group by with a limit? you say the duplicate might be the solution; what do you want to do here exactly? there's no delete in that link. – Funk Forty Niner Dec 28 '16 at 20:28
  • @Fred-ii- I believe I can modify the solution in the duplicate to delete instead of select. I want to delete rows from the table. I think I explained it fine in the question. – Tom Dec 28 '16 at 20:33
  • *"UPDATE: Are we sure this is a duplicate? I'm not selecting, I'm deleting. I think having an answer here would be beneficial."* - So in your saying to shmosel earlier *"Your duplicate suggestion might be the solution."* - the person who closed it based themselves on that. You should have read through that first before telling them that. As I mentioned earlier *"DELETE with HAVING and COUNT() if more than 5 with a WHERE clause ought to do it."* is one way to do this. – Funk Forty Niner Dec 28 '16 at 20:38
  • Yes, it's duplicate absolutely. Instead of `select` you can use the same answer for `delete` as well with little modification may be. So consider yourself putting some effort. If it's not duplicate then it would have been reopened by someone already commented earlier like @Fred-ii- – Rahul Dec 28 '16 at 20:38
  • @Rahul I'm not going to reopen in case you may be wondering. If the OP disagrees, then they'll have to ask you to reopen it. I won't go against anyone's judgement. I gave them a pseudo method but they didn't respond to it when I posted. However, there is no DELETE syntax in that dupe. – Funk Forty Niner Dec 28 '16 at 20:40
  • @Rahul it's hard to respond to everyone. comments are getting too long here. Let me look into your solution. – Tom Dec 28 '16 at 20:41
  • @Fred-ii-, sure Fred. in comment, I just meant to OP that you have the privilege to reopen single handedly. So in case my decision to close it is wrong then you can counterfeit it. – Rahul Dec 28 '16 at 20:42
  • @Rahul *"that you have the privilege to reopen single handedly"* as do you ;-) so.... if you feel (and the OP) that the question should be reopened, then I guess it probably should. – Funk Forty Niner Dec 28 '16 at 20:44
  • Well, reopened it but there is not much difference between the marked dupe though. – Rahul Dec 28 '16 at 20:52
  • [DELETE FROM HAVING COUNT(\*) in MySQL](http://stackoverflow.com/q/10554627/) --- [MYSQL delete all results having count(\*)=1](http://stackoverflow.com/q/1902014/) --- [MySQL DELETE With a Sub-Query using Having and Count](http://stackoverflow.com/q/4936838/) - Just to name a few; this after Googling (basically what I said originally) "delete with having and count mysql". One of those stand to be a duplicate. – Funk Forty Niner Dec 28 '16 at 20:56
  • @Fred-ii- I don't want to delete an entire group that has more than 5 rows as a group. I only want to delete the excess beyond 5 rows. – Tom Dec 28 '16 at 20:59
  • they were examples and good ones at that – Funk Forty Niner Dec 28 '16 at 21:00

1 Answers1

0

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
Tom
  • 917
  • 2
  • 12
  • 23