Assign order ID to different rows of a table based on their date order and grouped by customer ID in SQL Server 2012
I have a dataset for the products purchased by each customer, I want to place an order for their transactions by sorting the date columns.
For example:
SubID Date other columns OrderColumn
------------------------------------------------------------------------------
1 2010/08/12 ...... ?
1 2011/09/12 ....... ?
1 2013/09/12 ...... ?
2 2013/09/12 ....... ?
2 2011/04/03 ...... ?
I want to place an order for transactions for every customer.
Something like that:
SubID Date other columns OrderColumn
------------------------------------------------------------------------------
1 2010/08/12 ...... 1
1 2011/09/12 ....... 2
1 2013/09/12 ...... 3
2 2013/09/12 ....... 2
2 2011/04/03 ...... 1
thanks for the help
source to share
You can use window function ROW_NUMBER()
:
SELECT
SubID,
[Date],
OrderColumn = ROW_NUMBER() OVER (PARTITION BY SubID ORDER BY [Date] DESC)
FROM
dbo.YourTable
This is the "section" of your data using SubID
, and each "section" (or data group) will receive an incremental number starting with 1 - based on the order date
(Side note: having a column called simply Date
is very unfortunate - first of all, it's not very descriptive ... what date are you talking about here? Also, Date
is a reserved word (datatype) in SQL Server! You should aim to use something more meaningful - OrderDate
or DateCreated
or something that makes sense in your situation)
source to share