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.

Answers


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;

Need Your Help

HTML5 Canvas Tileset Animation

javascript html5 canvas

How to do in HTML5 canvas Image animating?