How do I delete every row except the last one for each day?
I have a table 开发者_Python百科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?
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.
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));
精彩评论