SQL Query to add row number that resets everytime the column value changes (SQL Server 2014)

1
votes

Was looking through Row_number() function in SQL Server to increment the counter every time the value changes. The issue is that I need it to reset on a change such that the same value can reappear at a later stage and the row_number still resets and starts from 1 back again when you order it by datetime instead of grouping all the same type together and assigning a rownumber.

For example:

TableA

when I do 

select 
    *, 
    row_number() over (partition by type order by datetime) Order 
from 
    TableA;

I get the below result (instead of the desired output shown above):

Output

2
sqlsql-serverrow-numbergaps-and-islands
Please provide intended output as well - Suresh Gajera
Output added in TableA column Row number - Sonu
@Sonu please don't provide input and output as images. - Amira Bedhiafi
The dates in the final result set are obfuscated but this looks like a gaps and island problem. - Larnu
The question is knocking me down, Whats that ? - XAMT

2 Answers

2
votes

Such is a type of Gaps-And-Islands problem.

You can solve it by first calculating a rank for the groups where a column's value changes.

Then use that rank in the row_number.

WITH CTE_DATA AS
(
    SELECT *
    , (ROW_NUMBER() OVER (ORDER BY [datetime]) 
     - ROW_NUMBER() OVER (PARTITION BY [type] ORDER BY [datetime])) AS Rnk
    FROM TableA
)
SELECT [type], [datetime]
, ROW_NUMBER() OVER (PARTITION BY [type], Rnk ORDER BY [datetime]) AS [Order]
FROM CTE_DATA
ORDER BY [datetime]
1
votes
SELECT [type],[datetime],ROW_NUMBER() OVER (PARTITION BY [Type],A - B ORDER BY A) AS 
RowNum FROM 
(
SELECT *,ROW_NUMBER() OVER ( ORDER BY [datetime] ASC) A 
        ,ROW_NUMBER() OVER (PARTITION BY [TYPE] ORDER BY [datetime] ASC) B  
FROM #tble1
)A
ORDER BY 2