Resetting Row number according to record data change

Question

I have got the set of data as follow

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 have tried to run this query

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.

Agbogla

which database - sql server, oracle etc. ? what is the logic for assigning the row number this way ? what are you trying to do finally ? – Divisor

Your partition by statement says that partitions are based solely on name. The result you got is the result you asked for. The result you seem to want is that partitions reset every time a run of name changes. I don't think you can do this with a simple window function. You'll need one or two levels of subquery. – Siqueiros

For future askers, this falls into the class of problems known as gaps-and-islands. – Fenella

Answer 1

You need to identify the groups of names that occur together. You can do this with a difference of row numbers. Then, use the grp for partitioning the 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 your sample 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 it works.

Inconsequent

This is a very useful solution, because it should work on any database which supports ROW_NUMBER() (which is most of them) +1. – Secondguess

thanks indeed this was helpful in my situation as well – Marylnmarylou

What an elegant solution. This kind of thing makes math look beautiful. – Hoashis