Delete all rows, except last 10 for each client that has related row(s) in the table in one query?
<p>So my situation is this:</p>

<p>Clients table - has client data etc, not too exciting</p>

<p>Recently Viewed table - table that has recently viewed things for the client(s), And has structure like this:</p>

<pre><code>( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
, client_id INT NOT NULL
, cookie_user_id INT NOT NULL
, hotel_id INT NOT NULL
, added DATETIME NOT NULL
, comment TEXT
,status TINYINT NOT NULL DEFAULE 1
);
</code></pre>

<p>I currently have a partially working SQL to delete rows in the recently viewed table that right now globally limits number of latest remaining undeleted records in it. This is how it looks like now</p>

<pre><code>DELETE FROM `recently_viewed`
WHERE `recently_viewed`.`id` NOT IN (
        SELECT id 
          FROM ( 
            SELECT `id` 
            FROM `recently_viewed`
            WHERE `client_id` IN (SELECT `id` FROM `klijenti`)
            ORDER BY `id` DESC 
            LIMIT 5
        ) x 
     )
AND `client_id` &lt;&gt; 0
</code></pre>

<p>"LIMIT 5" part should limit to the N records to remain in recently viewed table on a "per client" basis. Right now it limits records in recently viewed table to 5 no matter how many clients actually have records there. So if I have 10 clients, each of them has 8 records in that table, I would like this query to delete as many oldest records as needed to leave only 5 newest recently viewed items for EACH client and not just leave 5 overall in the table, ignoring the "per each client" logic. Hope that makes sense to you :)</p>

<p>Currently, this query would be ok if I would first fetch all clients in the app and then do a foreach loop to make another query for each client and leave 5 of his latest recently viewed items, but would like to do this in one SQL query instead.</p>

<p>How could this be done ? Thank you</p>