Удалить все кроме последних N строк

#mysql

Здравствуйте.
Подскажите, пожалуйста, как реализовать такой запрос.
Структура таблицы:
USERNAME    |  DATETIME  |   IP_ADDRESS
user0
user0
user0
user1
user1
user1
user2
user2
user2
...
userN
userN
userN

CREATE TABLE `log` (
    `USERNAME` VARCHAR(50) NOT NULL,
    `DATETIME` DATETIME NOT NULL,
    `IP_ADDRESS` VARCHAR(15) NOT NULL
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM;

Мне нужно, чтобы для каждого пользователя в таблице было не больше 100 записей. Соответственно,
если их больше, более ранние нужно удалить.
Вот такой запрос как-бы работает 
delete from `log` 
    where `USERNAME` = ? and `DATETIME` not in (select `DATETIME` from (select *
from `log` order by `DATETIME` desc limit 100) s )

но я не могу повторять его для каждого USERNAME, т.к их несколько тысяч    

Ответы

Ответ 1

Судя по вопросу автору необходимо производить такую чистку регулярно, поэтому посоветую
создать хранимую процедуру для удаления логов. Внутри её делать удаление поэтапным,
с минимумом удаляемых записей за запрос, в моем примере 100 записей.

CREATE PROCEDURE logclean()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE users VARCHAR(50);
  DECLARE count_rows, delete_rows INT;

  DECLARE del CURSOR FOR
  SELECT `USERNAME`, Count(*) - 100 FROM `log`
  GROUP BY `USERNAME`
  HAVING Count(*) > 100;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN del;

  read_loop: LOOP
    FETCH del INTO users, count_rows;

    IF done THEN
      LEAVE read_loop;
    END IF;

    SET delete_rows = 100;

    WHILE (count_rows > 0)
      DO
        IF delete_rows > count_rows THEN SET delete_rows = count_rows;
        END IF;

        DELETE FROM `log`
        WHERE `USERNAME` = users
        ORDER BY `DATETIME`
        LIMIT delete_rows;

        SET count_rows = count_rows - ROW_COUNT();
      END WHILE;

  END LOOP;

  CLOSE del;
END;


Вот пример на SQL Fiddle.

PS. Запрос приведенный в вопросе неправильный, так как во вложенном запросе не учитывается
пользователь указанный в условии.


Ответ 2

Попробуйте это 
http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/. 

Статья называется "How to select the first/least/max row per group in SQL"