How to delete record from table if its count > 5

Question

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 ?

Answer 1

Try this to keep the latest three records that have system_id equal to 1 and count is greater than 5:

DELETE FROM <table_name> WHERE system_id = 1 AND value > 5 ORDER BY id DESC LIMIT 3

Answer 2

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.

Answer 3

You can specify an offset with the LIMIT keyword in your query so the newest 5 rows are kept. According to MySQL's documentation, however, there's no easy way to limit from an offset all the way to the last; instead, they suggest this:

To retrieve all rows from a certain offset up to the end of the result set, you can use some large number for the second parameter.

So this SQL should do the trick:

DELETE FROM table where systemid = 1 ORDER BY value DESC LIMIT 5,45484848

Answer 4

This is going to be a very long query. What you need is for every system_id that have more than 5 records you want to delete the record that are less than ranking 3.

I'm going to seperate the queries and use names for them at the end.

1_ ranking_query: abriviation is RQ

In mysql there is no rownum or something like that hope this query return the records ranked from ghe first to the last.

       SELECT 
               @rank := @rank + 1 as rank, 
               id, 
               system_id,
               value
       FROM table_name, (select @rank:=0) as rt
       order by system_id, value desc

this will rank every record in your table and the main benefit is the record with the same system_id will be after each other in desc order

            system_id       value      rank
               1.                     10.         1

                1.                     9.           2

                1.                     7.           3

                1.                     5.           4

                1.                     3.            5

                1.                      2.           6

                2.                    12.          7

                2.                    10.          8

                3.                     11.         9
                ........
                ......
                 3.                     1.          15

In this example for system_id 1 we need to keep only the three first (1,2,3) record same thing for system_id 3 (9,10,11)

2_ filter_query. Abriviation is: FQ Because you want to delete based on count 5 we need this extra query

        SELECT
                 system_id
        FROM table_name
        GROUP BY system_id 
        having count(*) > 5

result:

                     system_id
                        1
                        3

4_ third_query abriviation: RQD

a query to know which rank should we start do delete from for every system_id in mysql we need to rewrite the first query again but here i'm going to use Abriviation to keep the answer short.

         SELECT 
               system_id,
               min_rank + 3  from_rank
        FROM (
               SELECT
                      RQ2.system_id,
                      min(RQ2.rank) as  min_rank
              FROM (rank_query) RQ2
              GROUP BY system_id) RS

so for the same example the we going to have this result

         system_id.    from_rank 
            1.                     4
            2.                     9    -- this record will be removed by the filter_query
            3.                     12

FINAL QUERY:

so we need to delete record that exist in filter query and the ranking is bigger than from_rank.

        DELETE FROM table_name WHERE 
              id in (
                 SELECT
                     RQ.id
                 FROM
                            rank_query RQ INNER JOIN filter_query FQ ON rq.system_id = FQ.system_id
                                                       INNER JOIN third_query RQD ON RQ.rank >= RQD.from_rank)

I hope this idea work sorry for any syntax error i used my phone to answer i like this kind of query