Delete all but 5 newest entries in MySQL table
Posted
by manyxcxi
on Stack Overflow
See other posts from Stack Overflow
or by manyxcxi
Published on 2010-04-28T16:16:33Z
Indexed on
2010/04/28
16:23 UTC
Read the original article
Hit count: 291
I currently have PHP code that handles the logic for this because I do not know how to handle it in SQL. I want to create a stored procedure that will select all the elements in a table for a given run_id and delete all of them except for the 'newest' 5 entries (as noted by the stop_time column).
CREATE TABLE `TAA`.`RunHistory` (
`id` int(11) NOT NULL auto_increment,
`start_time` datetime default NULL,
`stop_time` datetime default NULL,
`success_lines` int(11) default NULL,
`error_lines` int(11) default NULL,
`config_id` int(11) NOT NULL,
`file_id` int(11) NOT NULL,
`notes` text NOT NULL,
`log_file` longblob,
`save` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=128 DEFAULT CHARSET=utf8;
© Stack Overflow or respective owner