Re: Delete all rows in table but keep the last recent 2

[Garey Guan]

mysql> select * from t;
+------+-------------+
| idx | create_date |
+------+-------------+
| 1 | 2009-09-19 |
| 3 | 2009-09-20 |
| 4 | 2009-09-22 |
| 6 | 2009-09-24 |
| 8 | 2009-09-26 |
+------+-------------+
5 rows in set (0.00 sec)

mysql> delete from t order by create_date desc limit 2;
Query OK, 2 rows affected (0.01 sec)

mysql> select * from t;
+------+-------------+
| idx | create_date |
+------+-------------+
| 1 | 2009-09-19 |
| 3 | 2009-09-20 |
| 4 | 2009-09-22 |
+------+-------------+
3 rows in set (0.00 sec)

mysql>