[FIXED] How to delete record from table if its count > 5

Issue

I have following table:

id  systemid    value
1      1           0
2      1           1
3      1           3
4      1           4
6      1           9
8      1           10
9      1           11
10     1           12

Now here i have 8 records of systemid = 1 so now i want to keep only latest 3 records (desc order) and delete older records whose systemid=1

I want output like :

id    systemid    value
 8     1            10
 9     1            11
 10    1            12

I just want to delete old records of systemid=1 only if its count > 5 and keep its latest 3 records.

How can i do this in query ?

Solution

If you do not always have 8 records and want to select the last 3 records from the table where systemid=1 however many records there are, then a good way to do this is to use the IN selector in your SQL statement.

It would be good is you could do this simply using the statement

SELECT * FROM mytable WHERE id IN (SELECT id FROM `mytable` WHERE systemid=1 ORDER BY id DESC LIMIT 3)

However this is not yet supported in MySQL and if you try this then you will get an error like

...doesn't yet support 'LIMIT & IN/ALL/SOME subquery'

So you need a workaround as follows (using SELECT to test):

SET @myvar := (SELECT GROUP_CONCAT(id SEPARATOR ',') AS myval FROM (SELECT * FROM `mytable` WHERE systemid=1 ORDER BY id DESC LIMIT 3 ) A GROUP BY A.systemid);
SELECT * FROM mytable WHERE FIND_IN_SET(id,@myvar);

The way that this works (first line) is to set a variable called @myvar which will hold the last 3 values as a comma separated string if id values. In your case

9,8,10

Then select the rows where the 'id' is in this string.

Replace the 'SELECT *' with 'DELETE FROM' to finalize the result so your query will be

SET @myvar := (SELECT GROUP_CONCAT(id SEPARATOR ',') AS myval FROM (SELECT * FROM `mytable` WHERE systemid=1 ORDER BY id DESC LIMIT 3 ) A GROUP BY A.systemid);
DELETE FROM mytable WHERE NOT FIND_IN_SET(id,@myvar);

I hope that this helps.

Answered By - Clinton