Thanks for the ID column but that can still be problematic. If it's created using an IDENTITY column or from a SEQUENCE object, there's no guarantee that you won't have gaps over time unless the column IS and IDENTITY column AND the data was loaded from a file in a single threaded fashion by something like BCP or BULK INSERT... or so people will tell you. It's better to be safe than sorry.
The DENSE_RANK() method that Mark Cowne posted is certainly the right way to do this but... it needs a little bullet-proofing because of the gap problem I mentioned above. We need to guarantee that something is guaranteed to have no gaps in an ever increasing fashion. The only way to do that easily is to make one on the fly in the code, thusly...
WITH cte AS
(SELECT *, Contig = ROW_NUMBER() OVER (ORDER BY ID) --Contiguous, ever-increasing
,seq_number = DENSE_RANK() OVER(ORDER BY Contig - register_sequence)
Also, in the future, please submit your data in a readily consumable format. You'll get answers much more quickly and they'll be tested. Here's how I made the example to test the above. Please see the article at the first link in my signature line below for more info on the many advantages of taking that bit of extra time...
DROP TABLE IF EXISTS #TestTable
SELECT Code = CONVERT(CHAR(8),v.Code )
,register_sequence = CONVERT(INT ,v.register_sequence)
,registered_time = CONVERT(TIME ,v.registered_time )
,id = CONVERT(INT ,v.id )
,('4001_0_3',5,'08:04:08', 5) --Added this
,('4001_0_3',2,'08:47:17', 8) --Skipped an ID (it happens)
Here are the results from all that...
Since the data already supports it, here's another way to get the correct output in a guaranteed fashion that doesn't look quite as obnoxious.
SELECT *,seq_number = SUM(IIF(register_sequence=1,1,0)) OVER (ORDER BY ID)
ORDER BY ID