Hi Dominic,
> I think everyone is misunderstanding my problem as perhaps I didnt phrase it very well. > > I need to add a new autonumber field to my db, but I need to make sure when the numbers go into the field they are in the order of one of my other fields that is a date field. > > i.e. I have a field which shows the date someone became a member - but my current ID field doesnt match in order. > > So I need to add a new ID field so that numbers 1 - say 100 are say 1st Jan 00 then numbers 101 - say 201 are say 2nd Jan 00 etc. etc. So that the ID fields order matches the dates order. > > Now I can write SELECT * FROM table ORDER BY STARTDATE which orders the db correctly, but then how do I add an autonumber field to it and keep the records in that order? > Right - now things _are_ more clear :-) Nevertheless, I have to ask you: why is this a requirement? What is the auto-inc column supposed to be: if it's "only" a unique identifier, what does the order matter? As for a solution, I think the best thing you can do is rename this table, create a new table with the same strucutre, but WITH an auto-inc column and then do a INSERT INTO... SELECT ... FROM oldtable ORDER BY datecolumn. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
