Reset ROW_NUMBER() when previous partition ID changes

I am trying to reset the ROW_NUMBER() value when there is a change in one of the IDs when sequenced in datetime order. For example in the table below, when Location_ID changes, I want to reset the RowNum to 1. Here’s the statement I am using.

[RowNum] = ROW_NUMBER() OVER (PARTITION BY EventDate, Asset_ID, Location_ID ORDER BY Scan_Timestamp)

I get RowNumX, but want RowNumY.

EventDate	Scan_Timestamp	Asset_ID	Location_ID	RowNumX	RowNumY
9/1/2021	09/01/21 12:28 AM	30010712	996	1	1
9/1/2021	09/01/21 06:18 AM	30010712	30000372	1	1
9/1/2021	09/01/21 06:52 AM	30010712	30000345	1	1
9/1/2021	09/01/21 08:43 AM	30010712	996	2	1 Reset (Loc_ID changed)
9/1/2021	09/01/21 08:44 AM	30010712	996	3	2
9/1/2021	09/01/21 08:47 AM	30010712	30000402	1	1
9/1/2021	09/01/21 11:17 AM	30010712	996	4	1 Reset (Loc_ID changed)
9/1/2021	09/01/21 11:17 AM	30010712	997	1	1
9/1/2021	09/01/21 01:34 PM	30010712	997	2	2
9/1/2021	09/01/21 01:47 PM	30010712	30000402	2	1 Reset (Loc_ID changed)
9/1/2021	09/01/21 01:51 PM	30010712	997	3	1 Reset (Loc_ID changed)
9/1/2021	09/01/21 01:52 PM	30010712	997	4	2

I’m thinking I have to join using a CTE, but was hoping someone might see an obvious simple solution that I am overlooking.

Answer

This is a type of gaps-and-islands problem.

There are many solutions, here is one:

• Use LAG to identify rows where LocationID changes
• Use a windowed count to create a grouping ID for each island
• Then calculate the row-number using this grouping ID as a partitioning column

WITH Changes AS (
    SELECT *,
      IsChange = CASE WHEN LAG(Location_ID, 1, -999) OVER (PARTITION BY EventDate, Asset_ID
                      ORDER BY Scan_Timestamp) <> Location_ID THEN 1 END
    FROM YourTable t
),
Groups AS (
    SELECT *,
      GroupId = COUNT(IsChange) OVER (PARTITION BY EventDate, Asset_ID
                      ORDER BY Scan_Timestamp ROWS UNBOUNDED PRECEDING)
    FROM Changes
)
SELECT
  EventDate,
  Scan_Timestamp,
  Asset_ID,
  Location_ID,GroupId,IsChange,
  RowNumY = ROW_NUMBER() OVER (PARTITION BY EventDate, Asset_ID, GroupId ORDER BY Scan_Timestamp)
FROM Groups;

db<>fiddle