clear markdown help donate comments/suggestions/bugs a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith?
By using dbfiddle, you agree to license everything you submit by Creative Commons CC0

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]
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