Counting value changes in a table column

Question

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

Answer 1

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