Oracle: How to restart row_number function

Question

I have a scenario where in I need to assign row_number for a data grouped by department, branch, amount and ordering by date. If the amount repeats after some dates for the same department & branch, I would like to have the row_number as 1. Could you please let me know how to achieve this? When I tried with Row_number function the numbering is continuing after some dates.

Sample Data: This is the row numbers I am getting if the following function is used row_number() over(Partition by Department, Branch, Amount order by Date)

Department  Branch  Amount  Date        Row_number()
Dep A       Bran 1  51      25-Oct-12   1
Dep A       Bran 1  45.5    26-Nov-12   1
Dep A       Bran 1  45.5    05-Apr-13   2
Dep A       Bran 1  45.5    06-May-13   3
Dep A       Bran 1  65      07-May-13   1
Dep A       Bran 1  51      26-Aug-13   2
Dep A       Bran 1  51      11-Sep-13   3

But I am expecting output in the below order.

Department  Branch  Amount  Date        Row_number()
Dep A       Bran 1  51      25-Oct-12   1
Dep A       Bran 1  45.5    26-Nov-12   1
Dep A       Bran 1  45.5    05-Apr-13   2
Dep A       Bran 1  45.5    06-May-13   3
Dep A       Bran 1  65      07-May-13   1
Dep A       Bran 1  51      26-Aug-13   1
Dep A       Bran 1  51      11-Sep-13   2

Could anyone help me on this?

Answer 1

You need to change how you identify the groups inside which you calculate row_number().

Something like:

SQL> with t (Department,  Branch,  Amount,  Date#)
  2  as (
  3  select 'Dep A',   'Bran 1',  51,  to_date('25-10-2012','DD-MM-YYYY') from dual union all
  4  select 'Dep A',  'Bran 1',  45.5,    to_date('26-11-2012','DD-MM-YYYY') from dual union all
  5  select 'Dep A',   'Bran 1',  45.5,    to_date('05-04-2013','DD-MM-YYYY') from dual union all
  6  select 'Dep A',   'Bran 1', 45.5,    to_date('06-05-2013','DD-MM-YYYY') from dual union all
  7  select 'Dep A',   'Bran 1',  65,  to_date('07-05-2013','DD-MM-YYYY') from dual union all
  8  select 'Dep A',   'Bran 1',  51,  to_date('26-08-2013','DD-MM-YYYY') from dual union all
  9  select 'Dep A',   'Bran 1',  51,  to_date('11-09-2013','DD-MM-YYYY') from dual
 10  )
 11  select department, branch, amount, date#, row_number() over(partition by grp order by date#) rn
 12  from (
 13  select department, branch, amount, date#, sum(st_grp) over(order by date#) grp from (
 14  select department, branch, amount, date#, case when amount = lag(amount,1,amount) over(order by date#) then 0 else 1 end st_grp from t
 15  )
 16  )
 17  order by date#
 18  /

DEPAR BRANCH     AMOUNT DATE#            RN                                     
----- ------ ---------- -------- ----------                                     
Dep A Bran 1         51 25.10.12          1                                     
Dep A Bran 1       45,5 26.11.12          1                                     
Dep A Bran 1       45,5 05.04.13          2                                     
Dep A Bran 1       45,5 06.05.13          3                                     
Dep A Bran 1         65 07.05.13          1                                     
Dep A Bran 1         51 26.08.13          1                                     
Dep A Bran 1         51 11.09.13          2