sql - A running number column incrementing its value by conditon -

is there way create column running number increment value 1 whenever value in column has specific value? example, if have

  cola           c  change      b      c      b  change      b           .      .      .  

then have

cola          colb                1      c           1  change          2      b           2      c           2      b           2  change          3     b           3                3      .           .      .           .      .           .  

all of below solutions assume have explicit ordercriteria you're ordering events.

here's how you'd postgresql 9.4:

select   cola,   count(*) filter (where cola = 'change') on (order ordercriteria) + 1 colb, my_table order ordercriteria 

here's how you'd other rdbms supporting window functions:

(these include cubrid, db2, firebird 3, hana, informix, oracle, postgresql, redshift, sql server, sybase sql anywhere, , others)

select   cola,   count(case when cola = 'change' 1 end) on (order ordercriteria) + 1 colb, my_table order ordercriteria 

here's how you'd other rdbms:

select   cola,   (select count(*)    my_table t2    t2.ordercriteria <= t1.ordercriteria    , t2.cola = 'change') + 1 colb my_table t1 order ordercriteria