Deleting every nth row SQL

问题:

Say I have a table (id int, Name varchar) of 1000 rows. Now I wish to delete every nth record (every 2nd, 3rd or 5th) . What is the most efficient way to do this ?

回答1:

For SQL Server 2005+

Every 2nd row

WITH example AS (
    SELECT t.*, ROW_NUMBER() OVER (ORDER BY t.id) AS rank
       FROM TABLE t)
DELETE example
   WHERE rank%2 = 0

For every 3rd row, change the WHERE clause to:

WHERE rank%3 = 0

Anf for every fifth row:

WHERE rank%5 = 0

This uses modulus, which returns the remainder from division. If the remainder is zero, the value being divided is a multiple of the divisor.