CODEWITHSUNDEEP
sql
Phil Fry
Phil Fry

Reputation: 31

SQL: Removing extra rows, based on alternating values

I have a table that has the status of a user field, and the value is either 0 or 1. Due to a coding error created several years ago, a new entry is added whether the value is actually changed or not. The table has a DateModified column for when the entry was created.

What I want to do is reduce the table down to just alternating (oscillating?) values, with the earliest new value being the row that gets preserved every time. An example will make this much clearer:

What the table currently looks like:

    DateMod  Value
    6:05 pm    0
    6:01 pm    0
    5:47 pm    0
    5:33 pm    1
    5:15 pm    1
    4:07 pm    0
    3:58 pm    1
    2:23 pm    0

What the table should look like:

    DateMod   Value
    5:47 pm     0
    5:15 pm     1
    4:07 pm     0
    3:58 pm     1
    2:23 pm     0

Hopefully that makes sense. This must be possible, right?

Upvotes: 3

Views: 136

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138970

This will work in SQL Server 2008. I used time as data type for DateMod but it is the same if you have datetime instead. Common Table Express and row_number can be used from SQL Server 2005.

-- Sample table
declare @T table(DateMod time, Value bit)
insert into @T values
    ('6:05 pm',    0),
    ('6:01 pm',    0),
    ('5:47 pm',    0),
    ('5:33 pm',    1),
    ('5:15 pm',    1),
    ('4:07 pm',    0),
    ('3:58 pm',    1),
    ('2:23 pm',    0)

-- Delete using row_number and cte    
;with cte as
(
  select *,
         row_number() over(order by DateMod) as rn
  from @T
)    
delete C2
from cte as C1
  inner join cte as C2
    on C1.rn+1 = C2.rn and
       C1.Value = C2.Value

-- Result    
select *
from @T

Result:

DateMod          Value
---------------- -----
17:47:00.0000000 0
17:15:00.0000000 1
16:07:00.0000000 0
15:58:00.0000000 1
14:23:00.0000000 0

Upvotes: 5

Related Questions