question

siugoalie78 avatar image
siugoalie78 asked

Select Row Numbers that Reset based on another column

I need to be able to select data with multiple rows per person in the database for (I'll cal it Sales for simplicity's sake). For example, my sales data may look like Customer_ID Sales Date 555 1/1/2014 555 5/7/2014 555 6/4/2014 555 8/3/2014 666 2/12/2014 666 6/20/2014 777 12/8/2013 777 4/17/2014 777 4/18/2014 777 4/20/2014 777 6/18/2014 What I need is a third column which starts at 1 for each customer, and numbers through that customer's sales lines, so: Customer_ID Sales Date RowNumber 555 1/1/2014 1 555 5/7/2014 2 555 6/4/2014 3 555 8/3/2014 4 666 2/12/2014 1 666 6/20/2014 2 777 12/8/2013 1 777 4/17/2014 2 777 4/18/2014 3 777 4/20/2014 4 777 6/18/2014 5 Thanks!
tsqlrow_number
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
siugoalie78 avatar image
siugoalie78 answered
Figured it out - need to use the Partition by clause along with the Row_Number function
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.