Create Auto Number based on ORder by Field

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Programming » General » Create Auto Number based on ORder by Field

Create Auto Number based on ORder by Field

Rate Topic

Display Mode

Topic Options

Author

Message

arun_anand09

Posted Thursday, April 16, 2009 5:05 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, June 07, 2011 11:05 AM
Points: 15, Visits: 58

Hi Guys - This should be an easy one but need to confirm.

I need to alter a table and create Auto Number based on Order By ASCENDING a field which is a 13 digit numbers.

I normally use:
Alter Table [TableNAME]

add AutoNum Numeric(5,0) identity

But the above does not like ORder By statement,.

Please guide as soon as possible.

Thanks in Advance

Post #698289

RBarryYoung

Posted Thursday, April 16, 2009 8:13 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855, Visits: 9,374

arun_anand09 (4/16/2009)

But the above does not like ORder By statement,.

Could you clarify what you mean by this statement?

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."

Post #698487

Matt Miller (#4)

Posted Thursday, April 16, 2009 8:14 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:56 PM
Points: 6,997, Visits: 13,941

In order to accomplish what you're looking for you need to create a new empty table with the autonumber, then run an INSERT statement with the ORDER BY instruction attached. This will ensure that the data gets numbered appropriately.

You will then need to do whatever table rename is relevant.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Post #698488

Vijaya Kadiyala

Posted Friday, April 17, 2009 9:45 AM

SSCommitted

Group: General Forum Members
Last Login: Monday, October 25, 2010 6:09 AM
Points: 1,621, Visits: 409

I am curios to know why do you want to store the "Oder by" in table.

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers

Post #699535

« Prev Topic | Next Topic »

Permissions