T-SQL without cursors

Hi there,

Say i have a table with the following structure:

Store Customer OrderNo
1 1 100
1 1 201
2 2 122
2 2 223
2 2 222
2 3 333

Is there an easy way to select sequences as below per Customer without using cursors

Store Customer OrderNo TransNo
1 1 100 1
1 1 201 2
2 2 122 1
2 2 223 2
2 2 222 3
2 3 333 1

Thanks

Answer this question

T-SQL without cursors

Jehan Badshah

create table CustomerOrder
(
Store int,
Customer int,
OrderNo int
)
Go
Insert INto CustomerOrder values (1,1,100)
Insert INto CustomerOrder values (1,1,201)
Insert INto CustomerOrder values (2,2,122)
Insert INto CustomerOrder values (2,2,223)
Insert INto CustomerOrder values (2,2,222)
Insert INto CustomerOrder values (2,3,333)
Go

select
Main.Store,
Main.Customer,
Main.OrderNo,
Count(Counter.OrderNo) TransNo
From
CustomerOrder Main
Join CustomerOrder Counter
On
Main.Store = Counter.Store
And Main.Customer = Counter.Customer
And Main.OrderNo >= Counter.OrderNo
Group By
Main.Store,
Main.Customer,
Main.OrderNo
wolf777
Great thanks this helps a lot. Brilliant!!!
jori0001

You need to use the window aggregate function ROW_NUMBER()

like this

select store, customer, orderno, row_number() over (partition by Store, Customer order by OrderNo)

from yourTable
T-SQL without cursors

Answer this question