Delete all but 5 new entries in the MySQL table.

Question

I currently have PHP code that processes the logic for this, because I do not know how to process it in SQL. I want to create a stored procedure that will delete all lines except the 5 most recent for this config_id. IE config_id = 5 is passed to the SP, so it knows which config_id it wants to clear.

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;

The newest one will be determined by start_time, if stop_time is NULL, but NOT the last, it must be deleted (stop_time can be null if the start was unceremoniously killed).

Answer 1

, MySQL 5.1.46, , LIMIT .

CREATE PROCEDURE DeleteBut5(IN c INT) BEGIN
  DECLARE i INT;
  DECLARE s DATETIME;

  SELECT id, stop_time INTO i, s
  FROM RunHistory WHERE config_id = c
  ORDER BY stop_time DESC, id DESC
  LIMIT 4, 1;

  DELETE FROM RunHistory WHERE stop_time < s OR stop_time = s AND id < i;
END

:

CREATE INDEX cov ON RunHistory (config_id, stop_time, id);

Answer 2

SQL-: , N?:

DELETE FROM `runHistory`
WHERE id NOT IN (
  SELECT id
  FROM (
    SELECT id
    FROM `runHistory`
    ORDER BY start_time DESC
    LIMIT 5 
  ) foo
);

Answer 3

begin;
declare v_start_time datetime;
declare v_id int;
#Find the id of the newest run
select id into v_id from RunHistory where start_time = (select max(start_time) from RunHistory);
#delete null stop times except for the newest run
delete from RunHistory where stop_time is null and id != v_id;
#first row is 0... skip 0-4, show 5
select start_time into v_start_time from RunHistory order by stop_time desc limit 4,1;
delete from RunHistory where start_time < v_start_time;
end;

. start_time. Stop_time . , . delete, , , , :

delete from RunHistory where stop_time is null and id != v_id order by start_time desc limit 5;

Answer 4

fooobar.com/questions/47045/... - . , .