Counting value changes in a table column

sql, sqlite

Solution

if ids are sequential with no gaps...

Select count(*)
From table t1
   join table t2 
       on t2.id = t1.id + 1
where t2.value <> t1.value

else...

Select count(*)
From table t1
   join table t2 
       on t2.id = (Select min(id)
                   From table 
                   where id > t1.id)
where t2.value <> t1.value

Problem

Consider a sorted table (according to id). How to count number of changes of the values in column 'value'? In the following example, the number of changes is 3 (10 to 20, 20 to 10, 10 to 30). Thx ``` id value 1 10 2 10 3 20 4 20 5 10 6 30 7 30 ```

Original source