mtmacdonald mtmacdonald - 25 days ago 12
SQL Question

DELETE with GROUP BY and aggregate conditions

I have a table that includes a foreign key and a DATETIME timestamp. I want to delete all rows where the timestamp is later than the beginning of the day of the final timestamp, but for each item separately.

Logically I want something like this:

DELETE FROM Entries
WHERE StartTime > CONVERT(Date,MAX(StartTime))
GROUP BY ItemId;


But I can't use GROUP BY in a DELETE, nor can I use SUM() in a WHERE condition.

I can delete them one item at at time using a subquery like this:

DELETE FROM Entries
WHERE ItemId=@Id
AND StartTime > (
SELECT CONVERT(Date,MAX(StartTime)) FROM Entries
WHERE ItemId=@Id
)


But I want to delete for all items in a single query. How can I achieve this?

Answer Source

You can rewrite your query to not use @Id:

DELETE e
FROM Entries AS e
WHERE StartTime > (
   SELECT CONVERT(Date,MAX(StartTime)) FROM Entries ee
   WHERE ee.ItemId=e.Id
)