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?

Accepted Answer

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)
```

row_number reset

Solution

Problem