CODEWITHSUNDEEP
mysqlsql
Arthur Robaeys
Arthur Robaeys

Reputation: 355

remove all rows except the last 10 rows? Or max number of rows = 10?

i have a small question. Is it possible if you have a database, that you can keep only the last 10 records inserted in the table? Or that your table can only hold 10 records, so when a new record is inserted, it adds it but gets rid of the last inserted record?

I'm using mySQL and PhpMyAdmin.

Upvotes: 0

Views: 875

Answers (2)

Scott Jacobsen
Scott Jacobsen

Reputation: 985

Here's one way:

delete from my_table where id not in (select id from my_table order by id desc limit 10);

Assuming id is a number field that is incremented on each insert. Timestamps would also work.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You can do this, using a trigger.

I don't think I recommend actually doing this. There is overhead to deleting records. Instead, you can just create a view such as:

create view v_table as
    select t.*
    from t
    order by insertionDateTime desc
    limit 10;

This might seem expensive, but with an index on (insertionDateTime desc), the performance should be reasonable.

Upvotes: 1

Related Questions