Delete Top-N' Rows from a Table with some sort

问题:

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

回答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 

回答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
)