SQL query: Exclude partial duplicates from query result

Question

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?

Something like these should work for you: https://stackoverflow.com/questions/12239169/how-to-select-records-without-duplicate-on-just-one-field-in-sql https://stackoverflow.com/questions/4891676/removing-duplicates-from-a-sql-query-not-just-use-distinct — StackUseR, Aug 23 '17 at 09:31
(1) What if values are repeated? Say, Sensor 1 is 30, 30, 31, 31, 30, 30. (2) Are you really using SQL Server 2000? The time to upgrade was almost a decade ago. — Gordon Linoff, Aug 23 '17 at 10:33
I agree with Gordon, I cant use group by. I would like to upgrade but some customers are still running on 2000 :-(. — Pavel, Aug 23 '17 at 12:11

score 1 · Answer 1 · answered Aug 23 '17 at 09:38

1

Something like:

select your_table.* from your_table
join(
    select min(id) as mid from your_table group by value
) t
on your_table.id = t.mid

answered Aug 23 '17 at 09:38

Oto Shavadze

40,603
55
152
236

I guess you should group also by id_sensor – Radim Bača Aug 23 '17 at 09:41

score 1 · Accepted Answer · answered Aug 23 '17 at 10:36

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.

Thank you Gordon, that is pretty close. – Pavel Aug 23 '17 at 12:05 — Pavel, Aug 23 '17 at 12:05

SQL query: Exclude partial duplicates from query result

2 Answers2