Detect changes in subsequent rows in a table
I have a table in which there are 2 columns, one is the timestamp and the other is a value field. The value field can change over time. The entries in the table are entered at every minute.
TimeStamp Value 1 0 2 0 3 0 4 0 5 2 6 6 7 6 8 7
I want to detect the rows where the value changes. I need and SQL that will return the result set as
5 2 6 6 8 7
and so on.
You can do this by finding the previous value. Here is a method using a correlated subquery:
select t.timestamp, t.value from (select t.*, (select top 1 t2.value from table t2 where t2.timestamp < t.timestamp order by t2.timestamp desc ) as prevvalue from table t ) t where prevvalue is null or prevvalue <> value;
If you are using SQL Server 2012, you can do this more easily using lag():
select t.timestamp, t.value from (select t.*, lag(value) over (order by timestamp desc) as prevvalue from table t ) t where prevvalue is null or prevvalue <> value;