row_number reset

Question

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?

Answer 1

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)