Create a new sort order without modifying the primary keys

[z178]

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

[Ender]

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.

[z178]

More clarification on the question soon..

[z178]

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.