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

Question

I use the following query to get the next element of a specific type from my table. I chose this request format to prevent race conditions if multiple servers try to get the next item. Question: how can I get the item with the lowest dtmLastRunDate? I tried to add "ORDER BY dtmLastRunDate", but it gives me "Wrong syntax next to the keyword" ORDER ".

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

Answer 1

Put it in the CTE as shown 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