How do I delete every row except the last one for each day?

Question

I have a table with stock quotes that looks something like this:

id, date, stock_id, value

Every day has several rows for each stock_id (it is automatically updated every five minutes), so the table is rather big at the moment.

How do i delete every row but the last one each day for every stock_id?

Answer 1

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));

Answer 2

The other answers don't make sure to keep 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 sequentially auto-numbered field, and date is a datetime field that at least contains the date, but my contain hour, minute, second, etc. as well.