Home > OS >  Reset ROW_NUMBER() when previous partition ID changes
Reset ROW_NUMBER() when previous partition ID changes

Time:10-05

I am trying to reset the ROW_NUMBER() value when there is a change 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 AM 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.

CodePudding user response:

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

  • Related