CODEWITHSUNDEEP
sqlsql-server
Jess8766
Jess8766

Reputation: 417

Reset ROW_NUMBER based on row value and previous row value?

I followed the steps below to successfully reset my SQL row_number based on a value. However I have now been tasked to reset them based on the previous value in the table as well.

Creating a rank that resets on a specific value of a column

My data looks like the below

SPID       Status     Product     
29333         7         120
29333         6         233   
29333         4         555
29333         2         777
29333         7         876
29333         7         983

I am trying to add a row_number column (partitioned by SPID, ordered by Product asc) which will reset the RN each time the the previous status = 7. My output should look like the below

SPID       Status     Product     RN   
29333         7         120       1
29333         6         233       1
29333         4         555       2
29333         2         777       3
29333         7         876       4
29333         7         983       1

I assume I would need to do something with the LAG function but to be honest I haven't used this before and wouldn't know how to incorporate it into my code? Does anyone have any advice? I am using SQL Server 2016

Appreciate your help

Upvotes: 0

Views: 935

Answers (1)

Thom A
Thom A

Reputation: 95544

One method would actually be to use a windowed COUNT to count the number of 7 status's previously in the data. This would give each "set" of rows a "group number" to partition in the OVER clause of your ROW_NUMBER function:

--Sample data
WITH YourTable AS(
    SELECT *
    FROM (VALUES(29333,7,120),
                (29333,6,233),
                (29333,4,555),
                (29333,2,777),
                (29333,7,876),
                (29333,7,983))V(SPID,Status,Product)),
--Solution
Grps AS(
    SELECT SPID,
           Status,
           Product,
           COUNT(CASE STATUS WHEN 7 THEN 1 END)  OVER (PARTITION BY SPID ORDER BY Product ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS SevenGroup
    FROM YourTable)
SELECT SPID,
       Status,
       Product,
       ROW_NUMBER() OVER (PARTITION BY SPID, SevenGroup ORDER BY Product) AS RN
FROM Grps;

Upvotes: 2

Related Questions