MolC MolC - 3 months ago 14
SQL Question

Sequence number for Service-Based Database

I’m trying to figure out, if any row in my local database (Service-Based Database) is removed, I want give to second row (next after deleted) index of deleted line with same step back for all subsequent rows of course, also important to keep original order of lines as it was inserted. So same question I’ve start here: How to setup auto increment for Service-Based Database but now I'm looking for a different way of solving. I want this number of row for some calculations to read line. It soon became clear that it is better not to do it this way as I’ve tried it here Reset auto increment so seems I need numbers sequence. So in this case index numbers means sequence number, for example:



  1. line 1

  2. line 2

  3. line 3

  4. line 4




If line2 is deleted I want this result:



  1. line 1

  2. line 3

  3. line 4




If all lines deleted, and then inserted new line becomes 1. new first line and not 5. new first line as it works with index now.

Answer

I think you should use an auto-incremented field (say ID) to "keep original order of lines as it was inserted". And use a VIEW for example to select rows from a table using ROW_NUMBER() function to emulate sequence number

CREATE VIEW T_WITH_ROW_ID  
AS
SELECT ROW_NUMBER() OVER(ORDER BY ID) as ROW_ID,
       T.*
       FROM T

and use it as

SELECT * FROM T_WITH_ROW_ID