Delete every alternate row in SQL

Question

I need to clear a table containing three columns, identifier (uniqueidentifier), Observation (nvarchar) and Timestamp (datetimeoffset). The content is generated by two sensors at intervals of 1 hour, and the Observation value is the same for the two sensors. My idea is to make a SELECT query like

SELECT * FROM [Records] ORDER BY [Timestamp] 

and then delete each alternate row.

I found this on SO how to delete every alternate row in the access table , but actually it doesn’t apply here, since the identifier is not Int, but UID.

The sample data will look like this:

Answer 1

If you are running 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 

Answer 2

Instead of deleting alternative records, you can use a safer change - delete only duplicate cases so that in case of an error in the data you will not 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 

Answer 3

If I'm not mistaken, you can use CTE, with row_number () to efficiently generate the temp numeric identifier column. Then you can apply the same idea - delete where the line number of 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 

Answer 4

you can do this using a MOD statement that divides two numbers and returns the remainder

to try

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

put it inside the loop to remove all alternate rows

eg,

 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 the correct syntax for access hope this helps