Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL: Removing extra rows, based on alternating values

Tags:

sql

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?

like image 271
Phil Fry Avatar asked Feb 04 '26 14:02

Phil Fry


1 Answers

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
like image 174
Mikael Eriksson Avatar answered Feb 06 '26 04:02

Mikael Eriksson



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!