You can’t DELETE TOP (x) with an ORDER BY
8September 19, 2018 by Kenneth Fisher
Did you know you can’t do this?
DELETE TOP (10) FROM SalesOrderDetail ORDER BY SalesOrderID DESC;
Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword ‘ORDER’.
I didn’t. Until I tried it anyway. Turns out, it says so right in the limitations section of BOL. Fortunately, that same section does have a recommendation on how to move forward.
If you need to use TOP to delete rows in a meaningful chronological order, you must use TOP together with an ORDER BY clause in a subselect statement.
Here is my version of the example they mentioned.
DELETE FROM SalesOrderDetail
WHERE SalesOrderDetailID IN
(SELECT TOP 10 SalesOrderDetailID
FROM SalesOrderDetail
ORDER BY SalesOrderID DESC);
GO
And this works fine. But I remembered that you can update (and probably delete) from a CTE.
Well, I hadn’t tried it out before, so let’s give it a shot.
WITH MyCTE AS ( SELECT TOP (10) * FROM SalesOrderDetail ORDER BY SalesOrderID DESC) DELETE FROM MyCTE;
Well, the code is shorter. And it does work! So how about performance? Well, I checked the IO, and much like with the UPDATE it uses about half the IO (which when you look at the extra join in the subquery kind of makes sense). I ran both pieces of code 50 times and the subquery runs at about 62ms while the CTE runs at 48ms. Not a big difference, but these are pretty well indexed and very small. I’m going to go with the CTE as the better version.
SQL Studies 
I learn a lot from your SQL posts. I like that they are more applied than theory. Keep it up! And, thanks!
Thanks! Glad you like it 🙂
–try
SET ROWCOUNT 10
DELETE FROM SalesOrderDetail
GO 5
Unfortunately SET ROWCOUNT has some serious drawbacks. And in fact it’s been deprecated for DELETEs.
https://sqlstudies.com/2013/10/07/use-top-instead-of-set-rowcount/
https://docs.microsoft.com/en-us/sql/t-sql/statements/set-rowcount-transact-sql?view=sql-server-2017
[…] Kenneth Fisher shows that deleting the top N records with an ORDER BY clause is not straightforward: […]
There is another form of the DELETE-statement that can be used with a subquery which is not too different to the second example:
select top(100) object_id into #gosh from sys.objects;
delete g
from ( select top(10) object_id from #gosh order by 1 desc ) x
inner join #gosh g on x.object_id = g.object_id
;
select * from #gosh;
drop table #gosh;
All these years and I don’t remember coming across this! Interesting – thanks
I know the feeling!