如何创建具有自动增量标识的列

我有一个'ACCOUNT'表，其中包含带有数据的'CreatedDate'列。我需要添加具有自动增量标识的新列'Order'。但是，添加新列时的扭曲是填充的值必须基于createddate列数据的排序顺序。然后在添加新行时，自动增量必须起作用。

即。如果我有两行CreatedDate 01-11-2017和15-11-2017，那么我需要的是新列必须具有CreatedDate 01-11-2017的值1和CreatedDate的15-11-2017

有人可以告诉你怎么做吗？



我尝试了什么：



我尝试添加没有自动增量标识的列，并将值设置为现有表数据。但之后我无法使该列自动递增，它需要删除另一列的主键属性，这在我的情况下无法完成

ie

I have a table 'ACCOUNT' with 'CreatedDate' column with data. I need to add new column 'Order' with auto increment identity. But the twist is while adding new column the value populated must be based on the sort order of createddate column data. Then while add new row the auto increment must function.
ie. If I have two rows with CreatedDate 01-11-2017 and 15-11-2017,then my need is the new column must have value 1 for CreatedDate 01-11-2017 and 2 for CreatedDate 15-11-2017
Can anybody tell how to do this?

What I have tried:

I tried to add column without auto increment identity and set values to existing table data. But after that I cannot make that column auto increment, it need to remove the primary key property of another column which cannot be done in my case
ie

ALTER TABLE ACCOUNT
   ADD ORDER BIGINT AUTO_INCREMENT

现在该列添加数据，但不是基于排序顺序of CreatedDate列。

Now the column add with data but is not based on the sort order of CreatedDate column.