You can’t DELETE TOP (x) with an ORDER BY

8

September 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.

8 thoughts on “You can’t DELETE TOP (x) with an ORDER BY

  1. Robert says:

    I learn a lot from your SQL posts. I like that they are more applied than theory. Keep it up! And, thanks!

  2. Stephen Morris says:

    –try

    SET ROWCOUNT 10

    DELETE FROM SalesOrderDetail

    GO 5

  3. […] Kenneth Fisher shows that deleting the top N records with an ORDER BY clause is not straightforward: […]

  4. Sean Redmond says:

    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;

  5. Jeff Mlakar says:

    All these years and I don’t remember coming across this! Interesting – thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,461 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: