Delete Every Alternate Row in SQL

问题:

I need to clean up a table which has three columns, ID (uniqueidentifier), Observation (nvarchar), and Timestamp (datetimeoffset). The content is generated by two sensors at 1 hour interval, and the value of Observation is the same from the two sensors. My idea is to do a SELECT query such as

SELECT * FROM [Records] ORDER BY [Timestamp]

and then delete every alternate row.

I found this on SO how to delete every alternate row in access table, but doesn't really applies here as the ID is not Int but UID.

A sample of the data would look like:

回答1:

If you are on SQL Server 2005 or later you can do something like this.

delete T
from (
       select row_number() over(order by [Timestamp]) as rn
       from YourTable
       where SomeColumn = @SomeValue
     ) T
where T.rn % 2 = 0

回答2:

Instead of deleting alternate records you might use safer variation - delete only duplicate Observations, so that in case of error in data you don't fall out of sync:

; with cte as (
  select *,
         row_number() over (partition by Observation 
                            order by [Timestamp]) rn
    from [Records]
)
delete cte
 where rn > 1

回答3:

If I'm not mistaken you could use a CTE, with row_number() to generate effectively a temp numeric id column. Then the same idea could be applied - delete where a row number mod 2 is 0:

;WITH temp
     AS (SELECT *,
                Row_number() OVER (ORDER BY [Timestamp]) rn
         FROM   [Records]
         ORDER  BY [Timestamp])
DELETE FROM temp
WHERE  rn % 2 = 0

回答4:

you can do so using the MOD operator which divide two numbers and return the remainder

try

DELETE FROM [Records] WHERE ([ID] Mod 2)=0)

put it inside a loop to delete all alternate rows

so for example

ID = 0;
rows = get_number_of_rows_in_the_table;
i = 0; // counter
while(i<rows) {
ID = get the ID of row 0
if(ID MOD 2 = 0) {
//perform delete operation
DELETE FROM [Records] WHERE ID=the ID you just got;
} else { 
increment the counter 
i++
}

this is one solution but not a correct syntax for access hope it helps