How To Delete First Or Last n Rows In MySQL

You can use this example database schema for this question or otherwise, you may use your database table for testing.

CREATE TABLE `students` (
  `id` int UNSIGNED NOT NULL,
  `stid` varchar(16) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE `students` ADD PRIMARY KEY (`id`);

Delete Records Using The Primary Key

This SQL query will delete every record which has a primary key id less than or equal to 100.

DELETE FROM `students` WHERE `id` <= 100;

This SQL query will delete every record which has an id greater than 900.

DELETE FROM `students` WHERE `id` > 900;

The above queries will delete records considering the value of id of the table. The problem is, what happens when the id of the table was not incremented exactly one by one. Or some of the records have been already removed. Or even the id is not numerical. Under that kind of situation, the above-mentioned queries will not work as expected.

Delete Records Using ORDER BY

This query will sort the entire table by created_at column and delete the first 100 records.

DELETE FROM `students` ORDER BY `created_at` ASC limit 100

We can use DESC (descending) instead of ASC (ascending) to invert the order of the above query, so it will delete the last 100 records.

DELETE FROM `students` ORDER BY `created_at` DESC limit 100

Feel free to visit devtonight.com for more related content.