Question: i have something like this:

```
DELETE FROM `history` WHERE `date_field` <= now() - INTERVAL 10 DAY

```

but if all records is older than 10 days - this query deletes all! i want to keep last 20 records, even if they too old !

Please help, what and how i need to upd my code, and what will be better use limit+offset of windowed function **OVER()** or need smth another ?

Answer: 
Join with a subquery that gets the most recent 20 days and excludes them.

```
DELETE h1 
FROM history AS h1
LEFT JOIN (
    SELECT id
    FROM history
    ORDER BY date_field DESC
    LIMIT 20
) AS h2 ON h1.id = h2.id
WHERE date_field < now() - INTERVAL 10 DAY
AND h2.id IS NULL;

```
How about not using `delete` at all? Write a query to save the records you want. Then truncate the table and insert them back in:

```
create temporary table tokeep as
    select h.*
    from history h
    where `date_field` > now() - INTERVAL 10 DAY
    union
    select h.*
    from history h
    order by date_field desc
    limit 20;

truncate table history;

insert into history  -- the only situation where I don't list the columns
    select *
    from tokeep;

```