Pavel Pavel - 1 month ago 14
SQL Question

SQL query: Exclude partial duplicates from query result

I have data from multiple sensors. Each record has own id, sensor id, timestamp and measured value. Like this:

|--id--|--id_sensor--|-------timestamp-------|--value--|
1 1 '2017-08-23 10:00:00' 30
2 1 '2017-08-23 10:02:00' 30
3 1 '2017-08-23 10:04:00' 31
4 1 '2017-08-23 10:06:00' 31
5 1 '2017-08-23 10:08:00' 32
6 2 '2017-08-23 10:00:00' 24
7 2 '2017-08-23 10:01:00' 24
8 2 '2017-08-23 10:02:00' 24
9 2 '2017-08-23 10:03:00' 24
10 2 '2017-08-23 10:04:00' 24
11 2 '2017-08-23 10:05:00' 24
12 2 '2017-08-23 10:06:00' 25


I would like to exclude record if the value did not change so the result should look like this:

|--id--|--id_sensor--|-------timestamp-------|--value--|
1 1 '2017-08-23 10:00:00' 30
3 1 '2017-08-23 10:04:00' 31
5 1 '2017-08-23 10:08:00' 32
6 2 '2017-08-23 10:00:00' 24
12 2 '2017-08-23 10:06:00' 25


I have to be compatible with sql server 2000 :-(
I would like to avoid using cursors if posible. Can anybody help?

Answer Source

I think the following may be closer to what you really want:

select t.*
from (select t.*,
             (select top 1 t2.value
              from t t2
              where t2.sensor = t.sensor and
                    t2.timestamp < t.timestamp
              order by t2.timestamp desc
             ) as prev_value
      from t
     ) t
where prev_value is null or prev_value <> value;

You really should upgrade to a supported version of SQL Server. Free versions are even available.