This is hard to explain so I will give an example. I need SQL (ms server), I assume its with row_number over partition but can't get it to work.
I have this table:
| ID | PreviousID | Data |
|---|---|---|
| 1 | a | |
| 2 | 1 | b |
| 3 | 2 | c |
| 4 | d | |
| 5 | 4 | e |
| 6 | f |
I want these results:
| ID | NewID | Data |
|---|---|---|
| 1 | 1 | a |
| 2 | 1 | b |
| 3 | 1 | c |
| 4 | 2 | d |
| 5 | 2 | e |
| 6 | 3 | f |
And another with just the new IDs of each sequence:
| NewID | Data |
|---|---|
| 1 | a |
| 2 | d |
| 3 | f |
Instead of a row number new id, it could also have the first id of the sequence, whatever is easier, as long as it identifies the sequence.
CodePudding user response：
Seems you want a windowed COUNT of rows where the value of PreviousID is NULL.
SELECT ID,
COUNT(CASE WHEN PreviousID IS NULL THEN 1 END) OVER (ORDER BY ID) AS NewID,
Data
FROM dbo.YourTable;