List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:November 25 2003 9:52am
Subject:Re: help needed!
View as plain text  
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

Thread
help needed!Dominic Hayhoe25 Nov
  • Re: help needed!Martijn Tonies25 Nov