删除所有行并保留最新的x [英] Delete all rows and keep latest x left

问题描述

我有一张桌子

entryid,  roomid
 1           1      
 2          55
 3           1
 4          12
 5           1
 6          44
 7           1
 8           3
 9           1

现在，我想删除roomid = 1的所有条目并保留 来自roomid的最新3个=剩下1个(仅需一个命令即可获得最好的结果)

Now I would like to delete ALL entries where roomid = 1 and keep the latest 3 from roomid = 1 left (best with just one command)

所以最终entryid:1& 3个被删除，entryid 6、7、9保持不变(确保所有其他roomid仍保持不变)

So finally entryid: 1 & 3 came deleted and entryid 6, 7, 9 keeps staying (for sure all other roomid will still stay)

感谢帮助.下面，我为有兴趣的每个人添加了自己的解决方案

Thanks for help. Below I added my own solution, for everyone interested

我开始了新问题如何将其纳入一个命令.您可以在那里帮助我.

I started a new Question how to bring that into ONE command. You may help me there.

推荐答案

DELETE支持ORDER BY和LIMIT子句，因此是可能的.但是，由于DELETE的引用限制和LIMIT的参数，您需要两个查询.

DELETE supports an ORDER BY and LIMIT clause, so it is possible. However, due to DELETE's referential restrictions and parameters of LIMIT you need two queries.

SELECT COUNT(*) AS total FROM table  WHERE roomid = 1;
-- run only if count is > 3
DELETE FROM table WHERE roomid = 1 LIMIT total - 3;

请注意，这可能需要中介技术.我已经显示了查询以供参考.

Please note this will probably require an intermediary technology. I have shown the queries for reference.

这篇关于删除所有行并保留最新的x的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！