CODEWITHSUNDEEP
sqlsql-server
user2261397
user2261397

Reputation: 81

row_number reset

I have a table.

prod, change, date
pr1, 'Yes', 2012-01-01
pr1, 'No' , 2012-02-01
pr1, 'No' , 2012-03-01
pr1, 'Yes', 2012-04-01
pr1, 'Yes', 2012-05-01
pr1, 'No' , 2012-06-01

I am trying to build a qry and use row_number() function in order to number rows. Just I need row_number() to reset each time change column has value yes. Then row_number() calculation has to start from beginning. Like this

prod, change, date, row_number
pr1, 'Yes', 2012-01-01, 1
pr1, 'No' , 2012-02-01, 2
pr1, 'No' , 2012-03-01, 3
pr1, 'Yes', 2012-04-01, 1
pr1, 'Yes', 2012-05-01, 1
pr1, 'No' , 2012-06-01, 2
... etc

is there a possibility to do something like that using only SQL? I was looking at window functions like row_number() over (order by date, prd_prod, change) but it does not work like that. are there any other options?

Upvotes: 7

Views: 12663

Answers (3)

Sameer Alibhai
Sameer Alibhai

Reputation: 3178

In order to reset the row_number, you have to add "PARTITION BY"

Before:

select RowOrder=ROW_NUMBER() OVER (ORDER BY WidgetTimeCreated)

After:

select RowOrder=ROW_NUMBER() OVER (PARTITION BY WidgetType ORDER BY WidgetTimeCreated)

Upvotes: 3

user359040
user359040

Reputation:

Try:

with yes as
(select y.*, row_number() over (partition by prod order by date) yesn
 from mytable y
 where change = 'Yes'),
yesrange as
(select c.*, n.[date] next_date
 from yes c
 left join yes n on c.prod = n.prod and c.yesn+1 = n.yesn)
select m.*, row_number() over (partition by m.prod, r.yesn order by m.date)
from mytable m
join yesrange r 
  on m.prod = r.prod and 
     m.[date] >= r.date and
     m.[date] < coalesce(r.next_date, dateadd(d, 1, m.[date]) )

(SQLFiddle here)

Upvotes: 1

muhmud
muhmud

Reputation: 4604

select *, row_number() over (partition by prod, 
                                (case when change = 'No' then (select max(date) from [<YourTable>] where prod = d.prod and change = 'Yes' and date < d.date)
                                      else date end) order by date) as rownum
from [<YourTable>] d

Upvotes: 1

Related Questions