MySql Больше 100 записей с одним полем — Удалить ОТ 100 по дате. Как такое запросить?

Question

[chelkaz]

Так я проверяю какие записи с полем point имеют количество больше 100

SELECT COUNT(*), point
FROM jobs
GROUP BY point
HAVING COUNT(*) > 100

А как сделать и возможно ли, одним запросом?
Мне нужно что бы старые записи удалялись, если их количество с одинаковым полем больше 100.
В базу постоянно добавляются записи, если записей получается больше 100 с одинаковое полем point
То нужно удалить у них самые старые по полю TIMESTAMP
Например записей 130 с одинаковым point И получается нужно удалить 30, что бы 100 осталось, но удалить самые старые по полю created_at оно имеет тип TIMESTAMP

Answer 1

[Евгений]

добавьте поле point_count и не мучайтесь

Comments

[chelkaz]

Куда добавить? Всем записям? И как Вы это видите? Что в него писать? Число всех записей с одинаковым полем point? То како смысл? Или номер записи с полем point? Не очень понял...

Answer 2

[Максим Федоров]

DELETE jobs.*
FROM
	jobs
INNER JOIN (
	SELECT
		COUNT(*) as cnt,
		MAX(datetime) AS datetime,
		point
	FROM
		jobs
	GROUP BY
		point
	HAVING
		cnt > 100
) AS t ON jobs.point = t.point
AND jobs.datetime < t.datetime

Comments

[romy4]

не удалит самые старые по полю timestamp

[chelkaz]

Этот запрос удаляет все подряд. Это jobs.point = jobs.point игнорируется. Сделал тест и почти все удалилось...

[Максим Федоров]

chelkaz: сори опечатался, исправил опечатку нужно не jobs.point = jobs.point а jobs.point = t.point

[chelkaz]

Максим Федоров: Такой запрос оставляет только одну запись и самую НОВУЮ) А все удаляет.

[chelkaz]

Этот запрос не выбирает именно те которых больше, например cnt > 3
Он делает так - Если их больше, то удаляет все кроме самой новой...

[Максим Федоров]

chelkaz: этот запрос удаляет все одниковые записи (у которых point равен) если их количество больше 100 оставляя самую последнюю. Насколько я понял из вопроса Вы именно это хотите сделать или я не правильно понял Вашу проблему?

[chelkaz]

Максим Федоров: Почти все верно, только вопрос был Удалить ОТ 100 по дате
Например записей 130 с одинаковым point И получается нужно удалить 30, что бы 100 осталось, но удалить самые старые по полю created_at оно имеет тип TIMESTAMP

Answer 3

[Alexeytur]

DELETE FROM jobs j WHERE 
(SELECT COUNT(*) FROM jobs j1 WHERE 
j1.point=j.point AND j1.datetime>j.datetime)>99

Comments

[chelkaz]

Скажите пожалуйста, а как будет вести себя запрос, если самые старые будет иметь как одинаковые point так и даты?
например 99 и 100 имеют одинаковый поинт: 12345 и одинаковую дату 2017-05-31 12:05:54
Что произойдет? Какой удалится?

[chelkaz]

Тестирую этим запросом и получаю ошибку синтаксиса... В чем ошибка не могу понять:

DELETE FROM users u WHERE 
(SELECT COUNT(*) FROM users u1 WHERE 
u1.name = u.name AND u1.created_at > created_at) > 2

И получаю ошибку... MySql 5.7.18

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'u WHERE 
(SELECT COUNT(*) FROM users u1 WHERE 
u1.name = u.name AND u1.created_a' at line 1

Пробовал и так `users` и так users AS u... Не могу понять где ошибка...

[Alexeytur]

chelkaz:
Похоже одним запросом (даже с подзапросами)не обойтись.
В мануале https://dev.mysql.com/doc/refman/5.7/en/delete.html написано:

Subqueries
You cannot delete from a table and select from the same table in a subquery.