mysql deleting oldest record in a table

Question

Hi all I am trying to come up with a sql that will delete the single oldest row in a database, currently I have this:

DELETE FROM `videos` WHERE date_added IS NOT NULL LIMIT 1

Will that only ever delete from the top of my database or will it pick at random?

Answer 1

You can use order by and try like this-

DELETE FROM `videos` WHERE date_added IS NOT NULL order by date_added asc LIMIT 1

For more info: http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html

Edited answer to update order by asc

Answer 2

You will need to order it by date, descending, so the first result is the oldest one, and then delete that first result.

DELETE FROM `videos` 
WHERE data_added IS NOT NULL 
ORDER BY date_added DESC 
LIMIT 1;

This solution will work as long as your date_added column is a MySQL Date type or a numeric type (e.g. Unix timestamp). If not, you shall cast the data or modify the structure of your database (I recommend the later).

In order to cast the data, you could do:

DELETE FROM `videos` 
WHERE data_added IS NOT NULL 
ORDER BY Cast(date_added as date) DESC 
LIMIT 1;