SQL Query

Is there a way to delete all the rows after the 10 newest rows with a sql query. (mysql)

Maybe...
DELETE FROM table
WHERE id < (MAX(id) - 10)

(If id column is auto-incremented)

but that assumes no rows within the newset 10 have been deleted already

I cant do it that way because he is right. SOme of the rows before or between have been deleted. Is there another way?

Correct point from crowdozer

Does this work?
DELETE FROM table
WHERE id < (SELECT id FROM table ORDER BY id DESC LIMIT 10,1)

I assume by 'after 10 newest rows' you mean delete anything older than the 10 newest rows?

Do limits work on deletes?

If so,

Code:

---

LOCK TABLE table

// mixing PHP and SQL here ;)
SELECT $count = COUNT( * )
  FROM table

If $count > 10
// Ok we know there are say 120 rows in the table.
// Delete the first 110 rows
DELETE TABLE
 ORDER BY id ASC
 LIMIT $count - 10

---

But more to the point -- why are you doing this? Maybe we can help engineer a better solution. :D