ROW_Count() To Start Over Based On Order

Question

Create Table #Test (
    ID Int Primary Key Identity,
    Category VarChar(100)
    )

Insert into #Test
(Category)
Values
('Banana'),
('Banana'),
('Banana'),
('Banana'),
('Banana'),
('Banana'),
('Strawberry'),
('Strawberry'),
('Strawberry'),
('Banana'),
('Banana')

Select
     *
    ,ROW_NUMBER() Over (Partition by Category order by ID) as RowNum

From #Test

Order by ID

So this script returns this:

ID  Category    RowNum
1   Banana      1
2   Banana      2
3   Banana      3
4   Banana      4
5   Banana      5
6   Banana      6
7   Strawberry  1
8   Strawberry  2
9   Strawberry  3
10  Banana      7
11  Banana      8

Which makes perfect sense, except I want it to return this:

ID  Category    RowNum
1   Banana      1
2   Banana      2
3   Banana      3
4   Banana      4
5   Banana      5
6   Banana      6
7   Strawberry  1
8   Strawberry  2
9   Strawberry  3
10  Banana      1
11  Banana      2

I want it to restart the count when it hits a new set of Banana. Obviously my data is not really bananas, but it makes it easy to visualize.

This recurrence of bananas is considered to be new, so we want to start counting from one when we see this. I've been racking my brain and can't think of a good way to do this. I understand why it is not working but can't think of a way to make it work. Any advice on the best way to do this?

Answer 1

There are several different ways to approach this. One method is the difference of row_number() approach. This method will identify groups of adjacent categories that are the same:

Select t.*,
       row_number() over (partition by grp, category order by id) as rownum
From (select t.*, 
             (row_number() over (order by id) -
              row_number() over (partition by category order by id)
             ) as grp
      from #Test t
     ) t
Order by ID;

You can also figure out the groupings using lag(), but this will work in SQL Server 2005 and 2008 as well as more recent versions.