Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Create Auto Number based on ORder by Field Expand / Collapse
Author
Message
Posted Thursday, April 16, 2009 5:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 7, 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
Posted Thursday, April 16, 2009 8:13 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, December 4, 2014 7:52 AM
Points: 9,294, Visits: 9,495
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
Posted Thursday, April 16, 2009 8:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 6:27 PM
Points: 7,179, Visits: 15,781
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
Posted Friday, April 17, 2009 9:45 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

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 »

Add to briefcase

Permissions Expand / Collapse