How to find the desired output using hive/sql

I have a table like this

col1	col2
First	row
Second	a
First	b
Second	row
First	c
Second	row

The output required is like below:

col1	col2	col3
First	row	1
Second	a	1
First	b	1
Second	row	2
First	c	2
Second	row	3

The logic is , whenever we are getting the value “row” in col2, the col3 will increment the counter, otherwise will skip it. Please help.

Answer

You need a column that specifies ordering because SQL tables represent unordered sets.

With such a column, just use conditional aggregation:

select t.*,
       sum(case when col2 = 'myval' then 1 else 0 end) over (order by <ordering col>) as col3
from t;