Delete All Rows And Keep Latest X Left

I have a table like entryid, roomid 1 1 2 55 3 1 4 12 5 1 6 44 7 1 8 3 9 1 N

Solution 1:

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.

SELECTCOUNT(*) AS total FROMtableWHERE roomid =1;
-- run only if count is > 3DELETEFROMtableWHERE roomid =1 LIMIT total -3;

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

Solution 2:

You can store the ids of the superfluous rooms in a temporary table, and delete based on that:

create temporary table tmpTable (id int);

insert  tmpTable
        (id)
select  id
from    YourTable yt
where   roomid =1and3<=
        (
        selectcount(*)
        from    YourTable yt2
        where   yt2.roomid = yt.roomid
                and yt2.id > yt.id
        );

deletefrom    YourTable
where   ID in (select id from tmpTable);    

This results in:

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

Solution 3:

SET@deleting= (SELECTCOUNT(*) FROM tbl WHERE roomid =1) -3;
-- run only if @deleting is > 0PREPARE stmt FROM'DELETE FROM tbl WHERE roomid = 1 ORDER BY entryid LIMIT ?';
EXECUTE stmt USING@deleting;

Solution 4:

Something like

deletefromTABLEwhere roomid=1and entryid notin 
   (select entryid fromTABLEwhere roomid=1orderby entryid desc limit 0, 3)

might work.

Solution 5:

T-SQL guy here, but can t-sql do:

SELECT*FROMTABLE A

    LEFTJOIN (SELECT TOP 3 entryID FROMTABLEWHERE roomID =1ORDERBY entryID DESC) B
    ON A.entryID = B.entryID 
WHERE       
    A.roomID =1AND
    B.entryID ISNULL

Then replace the select with DELETE TABLE FROM...

?