CODEWITHSUNDEEP
mysqlsqlmariadb
Oscar Fuentes
Oscar Fuentes

Reputation: 21

SQL - delete all except the last N rows by serial_numb

I have a table with a lot of records every second in each serial number. I want to get only the last 10 records of each serial number to be saved.

This is the structure of the database:enter image description here

I am trying this code but I get an error: This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

DELETE
    FROM instant_data m1
    WHERE id NOT IN (SELECT m2.id
                     FROM instant_data m2
                     WHERE m2.serial_numb = m1.serial_numb
                     ORDER BY m2.id DESC
                     LIMIT 30);

Upvotes: 0

Views: 515

Answers (1)

Akina
Akina

Reputation: 42728

DELETE instant_data 
FROM instant_data 
JOIN ( SELECT *, ROW_NUMBER() OVER (PARTITION BY serial_numb ORDER BY id DESC) rn 
       FROM instant_data  ) cte USING (id)
WHERE cte.rn > 10;

https://dbfiddle.uk/?rdbms=mariadb_10.6&fiddle=3a76cd142d538c7da687f595e0180729

Upvotes: 1

Related Questions