Reset line number according to a change in record data

Question

I have a dataset as follows

name date x 2014-01-01 x 2014-01-02 y 2014-01-03 x 2014-01-04 

and I'm trying to get this result

 name date row_num x 2014-01-01 1 x 2014-01-02 2 y 2014-01-03 1 x 2014-01-04 1 

I tried to run this request

 select name, date, row_number () over (partition by name order by date) as row_num from myTBL 

but unfortunately I get this result

 name date row_num x 2014-01-01 1 x 2014-01-02 2 y 2014-01-03 1 x 2014-01-04 3 

Please, help.

Answer 1

You need to identify groups of names that meet together. You can do this with the difference in row numbers. Then use grp to split row_number() :

 select name, date, row_number() over (partition by name, grp order by date) as row_num from (select t.*, (row_number() over (order by date) - row_number() over (partition by name order by date) ) as grp from myTBL t ) t 

For example data:

 name date 1st row_number 2nd Grp x 2014-01-01 1 1 0 x 2014-01-02 2 2 0 y 2014-01-03 3 1 2 x 2014-01-04 4 3 1 

This should give you an idea of ​​how this works.

Answer 2

I am looking for the same result as "2nd", but the only change I need is, I would like 3 to be 1, I need to count to reset after it switches from Y to X

name date 1st line number 2nd class x 2014-01-01 1 1 0 x 2014-01-02 2 2 0 y 2014-01-03 3 1 2 x 2014-01-04 4 3 1