Manga Black Manga Black - 8 days ago 6
MySQL Question

Mysql delete from Rows 20 onwards

I have an

SQL
statement,

select * from table1 where member_id='$member_id' order by date_time_modified desc;


which returns 48 rows, but I only want to the use the first 20 results.

How can I use a single
DELETE
statement to keep the first 20 rows and delete all of the rows after the 20th row (21 to 48).

Answer

You can use LIMIT in your delete query, just change your sort order to delete the last rows and not the firsts

DELETE
FROM table1
WHERE member_id='$member_id'
ORDER BY date_time_modified ASC
LIMIT 28
;