How to prune a table down to the first 5000 records of 50000

I have a rather large table of 50000 records, and I want to cut this down to 5000. How would I write an SQL query to delete the other 45000 records. The basic table structure contains the column of a datetime.

A rough idea of the query I want is the following

DELETE FROM mytable WHERE countexceeded(5000) ORDER BY filedate DESC;

I could write this in C# somehow grabbing the row index number and doing some work around that, however is there a tidy way to do 开发者_开发问答this?

---

The answer you have accepted is not valid syntax as DELETE does not allow an ORDER BY clause. You can use

;WITH T AS
(
SELECT TOP 45000 *
FROM mytable 
ORDER BY filedate 
)
DELETE FROM T

---

DELETE TOP(45000) FROM mytable ORDER BY filedate ASC;

Change the order by to ascending to get the rows in reverse order and then delete the top 45000.

Hope this helps.

Edit:-

I apologize for the invalid syntax. Here is my second attempt.

DELETE FROM myTable a INNER JOIN
(SELECT TOP(45000) * FROM myTable ORDER BY fileDate ASC) b ON a.id = b.id

If you do not have a unique column then please use Martin Smith's CTE answer.

---

if the table is correctly ordered:

DELETE FROM mytable LIMIT 5000

if not and the table has correctly ordered auto_increment index:

get the row

SELECT id, filedate FROM mytable LIMIT 1, 50000;

save the id and then delete

DELETE FROM mytable WHERE id >= @id;

if not ordered correctly, you could use filedate instead of id, but if it's a date without time, you could get undesired rows deleted from the same date, so be carefull with filedate deletion solution