sql - Counters in Teradata while inserting records -

i trying insert records in table in below format

name              amount       date       counter                      100        jan 1          1                  100        jan2           1                  200        jan 10         2                  300        mar 30         3 b                   50        jan 7          1 c                   20        jan 7          1 

could tell me sql generating value counter field . counter value should increment whenever amount changes , reset when name changes.

what need dense_rank function. unfortunately it's not natively implemented before td14.10, can written using nested olap-functions:

select    name    ,amount    ,date_col    ,sum(flag)     on (partition name           order date_col           rows unbounded preceding) "dense_rank"  (    select       name       ,amount       ,date_col       ,case           when amount = min(amount)                         on (partition name                                order date_col                               rows between 1 preceding , 1 preceding)           0            else 1        end flag    dropme  ) dt;