HOW TO ASSIGN CONSECUTIVE NUMBERS IN SQL
I have a SQL Server table with the following fields:
ID
PRODUCT
ORIGINATION DATE
It contains several different ID's, each with up to 40 PRODUCTs. I need to order the products by ORIGINATION DATE, and then assign a new field with the consecutive number. For example, source data:
ID 1
PRODUCT A
ORIGINATION DATE MARCH 2, 2012
ID 1
PRODUCT B
ORIGINATION DATE DECEMBER 10, 2011
ID 1
PRODUCTO C
ORIGINATION DATE DECEMBER 14, 2011
I need the following output:
ID 1
PRODUCT A
ORIGINATION DATE MARCH 2, 2012
CONSECUTIVE NUMBER: 3
ID 1
PRODUCT B
ORIGINATION DATE DECEMBER 10, 2011
CONSECUTIVE NUMBER: 1
ID 1
PRODUCTO C
ORIGINATION DATE DECEMBER 14, 2011
CONSECUTIVE NUMBER: 2
ID
PRODUCT
ORIGINATION DATE
It contains several different ID's, each with up to 40 PRODUCTs. I need to order the products by ORIGINATION DATE, and then assign a new field with the consecutive number. For example, source data:
ID 1
PRODUCT A
ORIGINATION DATE MARCH 2, 2012
ID 1
PRODUCT B
ORIGINATION DATE DECEMBER 10, 2011
ID 1
PRODUCTO C
ORIGINATION DATE DECEMBER 14, 2011
I need the following output:
ID 1
PRODUCT A
ORIGINATION DATE MARCH 2, 2012
CONSECUTIVE NUMBER: 3
ID 1
PRODUCT B
ORIGINATION DATE DECEMBER 10, 2011
CONSECUTIVE NUMBER: 1
ID 1
PRODUCTO C
ORIGINATION DATE DECEMBER 14, 2011
CONSECUTIVE NUMBER: 2
and if you want to reset the number within each ID try this
Select ID, PRODUCT, [ORIGINATION DATE] , Row_numer() over(partition by ID order by [ORIGINATION DATE]) [CONSECUTIVE NUMBER]
from tableName
Select ID, PRODUCT, [ORIGINATION DATE] , Row_numer() over(partition by ID order by [ORIGINATION DATE]) [CONSECUTIVE NUMBER]
from tableName
ASKER
I need to create the CONSECUTIVE NUMBER first, it would be a new field.
thanks
thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It works. Thanks
Select ID, PRODUCT, [ORIGINATION DATE] , Row_numer() over(order by [ORIGINATION DATE]) [CONSECUTIVE NUMBER]
from tableName