A Relational Database has records with a Primary Keys as serial numbers from 1, increasing by 1 with each record. I want to have the database modified later so that I can add records between already present records.

Say on September 1, 2007 my db has records with serial number 1 to 5. On September 4, 2007 I want to add a record so that it always comes after record 4 in the database. Rather than modifiying the entire column of PKs to accomodate the new record after record 4 how do I calculate a new serial number for the record set so that the set can be sorted based on this calculated serial number.

PK RecordName NewSerial

1................John

2................Charlie

3................Echo

4................Mathew

5................Nancy

If I now create a new record with PK 6 and want it to appear in position 2, what calculation or script should I do?

PK RecordName NewSerial

1................John...................1

2................Charlie................3

3................Echo...................4

4................Mathew.................5

5................Nancy..................6

6................Alpha..................2

How about just sorting the found set every time you navigate to the layout (this can be scripted easily). Presumably there's something about "Alpha" that makes him/her come in second, so sort on that field.

More clarification on the question soon..

Got the answer and done the script.

Its probably better to go by trial and error than waste time clarifying things so the database engine can eat my posts.