How to delete when the parameter varies by group without looping? (T-SQL)

Question

Imagine I have these columns in a table:

id int NOT NULL IDENTITY PRIMARY KEY, 
instant datetime NOT NULL,
foreignId bigint NOT NULL

For each group (grouped by foreignId) I want to delete all the rows which are 1 hour older than the max(instant). Thus, for each group the parameter is different.

Is it possible without looping?

Answer 1

Yep, it's pretty straightforward. Try this:

DELETE mt
FROM   MyTable AS mt
WHERE  mt.instant <= DATEADD(hh, -1, (SELECT MAX(instant) 
                                      FROM MyTable 
                                      WHERE ForeignID = mt.ForeignID))

Or this:

;WITH MostRecentKeys
AS
(SELECT ForeignID, MAX(instant) AS LatestInstant
FROM MyTable)

DELETE mt
FROM   MyTable AS mt
JOIN   MostRecentKeys mrk ON mt.ForeignID = mrt.ForeignID
       AND mt.Instant <= DATEADD(hh, -1, mrk.LatestInstant)

Answer 2

DELETE
FROM    mytable
FROM    mytable mto
WHERE   instant <
        (
        SELECT  DATEADD(hour, -1, MAX(instant))
        FROM    mytable mti
        WHERE   mti.foreignid = mto.foreignid
        )

Note double FROM clause, it's on purpose, otherwise you won't be able to alias the table you're deleting from.

The sample data to check:

DECLARE @mytable TABLE
        (
        id INT NOT NULL PRIMARY KEY,
        instant DATETIME NOT NULL,
        foreignID INT NOT NULL
        )

INSERT
INTO    @mytable
SELECT  1, '2009-22-07 10:00:00', 1
UNION ALL
SELECT  2, '2009-22-07 09:30:00', 1
UNION ALL
SELECT  3, '2009-22-07 08:00:00', 1
UNION ALL
SELECT  4, '2009-22-07 10:00:00', 2
UNION ALL
SELECT  5, '2009-22-07 08:00:00', 2
UNION ALL
SELECT  6, '2009-22-07 07:30:00', 2

DELETE
FROM    @mytable
FROM    @mytable mto
WHERE   instant <
        (
        SELECT  DATEADD(hour, -1, MAX(instant))
        FROM    @mytable mti
        WHERE   mti.foreignid = mto.foreignid
        )

SELECT  *
FROM    @mytable

1   2009-07-22 10:00:00.000 1
2   2009-07-22 09:30:00.000 1
4   2009-07-22 10:00:00.000 2

Answer 3

I'm going to assume when you say '1 hour older than the max(instant)' you mean '1 hour older than the max(instant) for that foreignId'.

Given that, there's almost certainly a more succinct way than this, but it will work:

DELETE
    TableName
WHERE
    DATEADD(hh, 1, instant) < (SELECT MAX(instant)
           FROM TableName T2
           WHERE T2.foreignId = TableName.foreignId)

The inner subquery is called a 'correlated subquery', if you want to look for more info. The way it works is that for each row under consideration by the outer query, it is the foreignId of that row that gets referenced by the subquery.