Question 63874 in PhpMyAdmin Question posted in
The official documentation can be found here.

SQL keep only the last X rows – Phpmyadmin

I have a table in which the id is constantly increasing. The Id does not always increase by the same value and it is not an auto_increment due to a api, but it increases permanently. Example:

Id other content
501 xxx
500 yyy
365 qqq
… …

Now I just want to keep the last X rows, remove rows with lower ids. I was able to find a similar but not a working solution for myself to this problem.

I thought that something like that would work, but there is an error:

DELETE FROM `games` WHERE `matchId` NOT IN(SELECT `matchId` FROM `games` ORDER BY `matchId` DESC LIMIT X)

#1235 – This version of MySQL doesn’t yet support ‘LIMIT &
IN/ALL/ANY/SOME subquery’

Im running the mysql version 8.0.27 on a phpmyadmin 5.1.1 web interface.

Is there a way to keep the newest x rows without triggering this error?

Tags:

2

Answers


  1. 0 
    WITH cte AS (
 SELECT id, ROW_NUMBER() OVER (ORDER BY id DESC) AS rownum FROM games
)
DELETE games FROM games JOIN cte USING (id) WHERE rownum > 2;
    Login or Signup to reply.
  2. 0

    You can use a CTE instead:

    with u as
(select Id from table_name order by Id desc limit 5)
delete from table_name
where Id not in(select Id from u)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search