Add and update a column with sequential numbering based on grouping

I have a table with following structure

Id	Division	Details
1	A	some text
2	A	some text
3	B	some text
4	B	some text
5	B	some text

I need to add a new column of integer type named “Order” with some data as described below:

Id	Division	Details	Order
1	A	some text	1
2	A	some text	2
3	B	some text	1
4	B	some text	2
5	B	some text	3

As we can see integer data in “Order” column sequence has to be reset if “Division” data changes. There’s are thousand of rows in the table, with more than 100 different divisions.

How can I achieve this?

Answer

You can use row_number():

select t.*, 
    row_number() over(partition by division order by id) rn
from mytable t