I do not want all the values in my table

Question

I have a table with 2.500.000 rows. All this rows has a value of timestamp. I want to keep 100 rows that has the biggest timestamp.. All the other values i want to be deleted!!

$values_to_delete = db_delete('error_log_jira')
    ->condition('timestamp', 1423731524, '>')
    ->execute();

I have this code and works perfect but i do not know how to crate the thing that i want is someone here that can help me?? I need a real help!!

how to create the things that i want .. what do you mean by that — Ameya Deshpande, Apr 15 '15 at 12:04
with the word things i mean the conditions that i told before — Karmen, Apr 15 '15 at 12:05
Please consider following answer: http://stackoverflow.com/questions/7142097/mysql-delete-statement-with-limit — Andriy, Apr 15 '15 at 12:10
This should really be marked as a duplicate of https://stackoverflow.com/questions/578867/sql-query-delete-all-records-from-the-table-except-latest-n. The duplicate question currently linked at the top has been deleted. — Ilmari Karonen, Apr 08 '18 at 21:08

score 0 · Accepted Answer · edited Jun 05 '15 at 19:48

Let's say your table which has 2.500.000 rows is called TableA. You can do this:

Create a temporary table TableB which has the same columns as TableB
Write a query which extracts 100 rows depending on the timestamp
Insert these 100 rows in TableB
Delete all the rows of TableA
Insert all the rows of TableB in TableA and drop TableB

Steps 2-4 can be done in sql by a query like this:

insert into TableB (SELECT * FROM TableA ORDER BY timestamp DESC LIMIT 100)

I do not want all the values in my table

1 Answers1