Jump to content
Sign in to follow this  
z178

Create a new sort order without modifying the primary keys

Recommended Posts

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.