SQL Server : using CTE row partition to serialize sequential timestamps

Question

I think I just need a little help with this but is there a way to incrementally count steps in SQL using some type of CTE row partition? I'm using SQL Server 2008 so won't be able to use the LAG function.

In the below, I am trying to find a way to calculate the Step Number as pictured below where for each unique ITEM in my table, in this case G43251, it calculates the process Step_Number based on the Date (timestamp) and the process type. For those with the same timestamp & process_type, it would label them both as the same Step_Number as there other fields that could cause the timestamp to repeat twice.

Right now I am playing around with this below and seeing how maybe I could fit in a DISTINCT timestamp methodology ? So that it doesn't count each row as something new.

WITH cte AS
(
    SELECT 
        *, 
        ROW_NUMBER() OVER (ORDER BY Timestamp_Posted DESC) 
            - ROW_NUMBER() OVER (PARTITION BY Item ORDER BY Timestamp_Posted Desc) rn 
    FROM 
        #t1
)
SELECT 
    *, 
    ROW_NUMBER() OVER (PARTITION BY Item, rn ORDER BY Timestamp_Posted DESC) rn2
FROM 
    cte
ORDER BY 
    Timestamp_Posted DESC

Answer 1

Please use dense_rank() instead of row_number()

SELECT *, dense_rank() OVER(Partition By Item ORDER BY Timestamp_Posted, Process_Type ) Step_Number
FROM  #t1
ORDER BY Timestamp_Posted DESC