It is relative hard to it now with SQL only. But we can simplify this task with window function that returns number of change in some column. Then this task can be solved by
select min(start), min(reason), sum(km)
from (select start, reason, km, change_number(reason) over (order by start))
group by change_number;
I guess that might be quite useful, otherwise the only way that comes to mind to do this would be something along the lines of:
select *,sum(case when reason <> lastreason then 1 else 0 end) over (order by start) as chg_num from (select *,lag(reason) over (order by start) vnext from sometable) sometable;
This way might not be too bad as I think the outer window will have no need to perform another sort, since the inner window clause has sorted it the right way already. Though something like change_number() would make this a bit more pretty. It's almost like rank(), but with a parameter.
