19

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.

Dale K
  • 25,246
  • 15
  • 42
  • 71
erezlale
  • 625
  • 2
  • 6
  • 17
  • 1
    which database - sql server, oracle etc. ? what is the logic for assigning the row number this way ? what are you trying to do finally ? – Erran Morad Dec 28 '14 at 22:10
  • 1
    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. – Ross Presser Dec 28 '14 at 22:15
  • 3
    For future askers, this falls into the class of problems known as [tag:gaps-and-islands]. – Clockwork-Muse Dec 28 '14 at 23:38

1 Answers1

33

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.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786