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

sql, sql-server, t-sql

Solution

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

Problem

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

Original source