How to delete every row except the last for every day?

Question

I have a stock quote table that looks something like this:

id, date, stock_id, value

Each day has several lines for each stock_id file (it is automatically updated every five minutes), so the table is currently quite large.

How to delete every line except the last every day for every stock_id file?

Answer 1

Other responses should not contain at least one record per stock_id per day. The following should do what you want.

 DELETE FROM StockQuotes WHERE id NOT IN ( SELECT MAX(id) FROM StockQuotes GROUP BY stock_id, DATE(`date`) ) 

Assuming id is a field with a sequential automatic number, and date is a datetime field that at least contains a date but also contains an hour, minute, second, etc.

Answer 2

I think this will do what you want:

 DELETE FROM STOCK_QUOTES WHERE ID NOT IN (SELECT MAX(ID) AS ID FROM STOCK_QUOTES GROUP BY DATE, STOCK_ID));