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

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  

Answer 2

Add the top 3 clause to the subselect:

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