Auto incrementing ID

Here is the senerio. I have 1 table (members) and an primary ID that is autoincrmenting. I rely on this ID to be in order i.e 1,2,3,4,5..... If I go and say do this

Delete from members where (ID = 3);

My order now reads... 1,2,4,5....
Is there anyway to execute a specific MySQL command to make those numbers sync up after deleting the spcific row? Thanks!!!!

If only one row is deleted and you know which row it is, then

> update table
> set id=id-1
> where id>3;

should work

The only reason I can think that you would want to do this is if you need to keep track of the order in which the records were entered.

If so, you'd be better off creating a field with the type DATETIME and assigning NOW() into it when the record is created.

Then you can sort by this field and even assign order numbers on the fly in your output without worrying about your autoincremented key being complete.

Just so everyone knows I finally figured out what I was doing. I ended up using a limit statment to grab the next 10 records. I changed my code around to only use the autoincrementing ID for updating, not displaying. Thanks to everyone who gave me ideas!