Paste this into a new question or an answer at dba.stackexchange.com:
<!-- --> > DECLARE @BuyList TABLE > ( > UserNo NVARCHAR(10), > StoreNo NVARCHAR(10), > ProductNo NVARCHAR(10), > [Order] INT > ) > INSERT INTO @BuyList > (UserNo, StoreNo, ProductNo, [Order]) > VALUES > ('001', 'S01', 'P01', 1), > ('001', 'S01', 'P02', 2), > ('001', 'S02', 'P02', 3), > ('001', 'S02', 'P03', 4), > ('001', 'S02', 'P04', 5), > ('001', 'S01', 'P05', 6), > ('001', 'S01', 'P06', 7), > ('001', 'S01', 'P07', 8), > ('002', 'S01', 'P08', 1), > ('002', 'S02', 'P09', 2), > ('003', 'S01', 'P01', 1), > ('003', 'S02', 'P01', 2), > ('003', 'S02', 'P02', 3) > > ;WITH CTE AS > ( > SELECT *, CASE WHEN LAG((UserNo + StoreNo)) OVER (ORDER BY UserNo, [Order]) > =(UserNo + StoreNo) THEN 0 ELSE 1 END Diff > FROM @BuyList > ) > > SELECT UserNo, StoreNo, ProductNo, [Order], > SUM(Diff) OVER (PARTITION BY UserNo ORDER BY UserNo, [Order]) AS StoreOrder > FROM CTE > ORDER BY UserNo, [Order] > GO > > <pre> > UserNo | StoreNo | ProductNo | Order | StoreOrder > :----- | :------ | :-------- | ----: | ---------: > 001 | S01 | P01 | 1 | 1 > 001 | S01 | P02 | 2 | 1 > 001 | S02 | P02 | 3 | 2 > 001 | S02 | P03 | 4 | 2 > 001 | S02 | P04 | 5 | 2 > 001 | S01 | P05 | 6 | 3 > 001 | S01 | P06 | 7 | 3 > 001 | S01 | P07 | 8 | 3 > 002 | S01 | P08 | 1 | 1 > 002 | S02 | P09 | 2 | 2 > 003 | S01 | P01 | 1 | 1 > 003 | S02 | P01 | 2 | 2 > 003 | S02 | P02 | 3 | 2 > </pre> *db<>fiddle [here](https://dbfiddle.uk/?rdbms=sqlserver_2017&rdbms2=sqlserver_2017&fiddle=aec9a0d6e76453b07f7d0842e46720e9)*
back to fiddle