Menu
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?
688
ROW_NUMBER function is a SQL ranking function that assigns a sequential rank number to each new record in a partition. When the SQL Server ROW NUMBER function detects two identical values in the same partition, it assigns different rank numbers to both.
Returns a unique row number for each row within a window partition. The row number starts at 1 and continues up sequentially.
The row_number() window function can be used without order by in over to arbitrarily assign a unique value to each row.
ROWNUM is the sequential number, allocated to each returned row during query execution. ROW_NUMBER assigns a number to each row according to its ordering within a group of rows. ROW_NUMBER is a function that returns numeric value.
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
