CODEWITHSUNDEEP
sqlsql-server-2008
Krumelur
Krumelur

Reputation: 33058

How to specify ORDER BY when using DELETE FROM in combination with OUTPUT?

I use the query below to get the next item of a specific type from my table. I chose this query format to prevent racing conditions if several servers try to get the next item. Question: how can I get the item that has the lowest dtmLastRunDate? I tried to add an "ORDER BY dtmLastRunDate" but it gives me "Incorrect Syntax near the keyword 'ORDER'".

DELETE TOP(1) FROM Schedule
WITH (READPAST)
OUTPUT DELETED.intUserID, DELETED.dtmLastRunDate
WHERE intScheduleType = @intScheduleType

Upvotes: 4

Views: 4691

Answers (1)

Martin Smith
Martin Smith

Reputation: 453453

Put it into a CTE as below.

;WITH T
     AS (SELECT TOP(1) *
         FROM   Schedule WITH (ROWLOCK, READPAST)
         WHERE  intScheduleType = @intScheduleType
         ORDER  BY dtmLastRunDate)
DELETE FROM T
OUTPUT DELETED.intUserID,
       DELETED.dtmLastRunDate

Upvotes: 6

Related Questions