SQL Query

[klassicd]

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

[jofa]

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

(If id column is auto-incremented)

[JimBolla]

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

[klassicd]

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

[jofa]

Correct point from crowdozer

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

[MattR]

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

Do limits work on deletes?

If so,

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

[MattR]

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