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

Now I would like to delete ALL entries wh开发者_运维问答ere roomid = 1 and keep the latest 3 from roomid = 1 left (best with just one command)

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

EDIT: 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 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.

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 = 1
        and 3 <=
        (
        select  count(*)
        from    YourTable yt2
        where   yt2.roomid = yt.roomid
                and yt2.id > yt.id
        );

delete  
from    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

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

Something like

delete from TABLE 
where roomid=1 
   and entryid not in 
   (select entryid from TABLE where roomid=1 order by entryid desc limit 0, 3)

might work.

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

SELECT
    *
FROM 
    TABLE A

    LEFT JOIN (SELECT TOP 3 entryID FROM TABLE WHERE roomID = 1 ORDER BY entryID DESC) B
    ON A.entryID = B.entryID 
WHERE       
    A.roomID = 1 AND
    B.entryID IS NULL

Then replace the select with DELETE TABLE FROM...

?

Thanks for all your Help.. I took them all together and use now this Solution :) For me this Step is closed. Thanks.

// Delete older comments from room 1 (keep last 3 left)
// Step 1:
$sql_com = "SELECT id FROM `mytable` WHERE roomid = '1'";
$result = mysql_query ($sql_com); $num_rows = mysql_num_rows($result);       

// Step 2:
if ($num_rows > 3) {
  $sql_com = "SELECT id FROM `mytable` WHERE roomid = '1' ORDER BY id DESC LIMIT 3,1";  
  $result = mysql_query ($sql_com);
  $row = mysql_fetch_array($result, MYSQL_NUM);
}

// Step 3:  
$sql_com = "DELETE FROM `mytable` WHERE roomid = '1' AND id < ".$row[0];
$result = mysql_query ($sql_com);