sql - Delete Top-N' Rows from a Table with some sorting(order by 'Column')

Question

I am having some confusion regarding Deleting the top N Rows order by some column.

I created have an example here Example at fiddle

What is wrong with these queries?

 Delete Top(3) from Table1 order by id desc

 Delete Top(3) from Table1 
 where id IN (select id from Table1 order by id desc)

Since in mysql the limit keyword does the job very well

question from:https://stackoverflow.com/questions/13508280/delete-top-n-rows-from-a-table-with-some-sortingorder-by-column

Answer 1

You can use a CTE to do a faster ordered delete without the need for a separate sub query to retrieve the top 3 ids.

WITH T
     AS (SELECT TOP 3 *
         FROM   Table1
         ORDER  BY id DESC)
DELETE FROM T