how to get automatic assigned id

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

how to get automatic assigned id

Author

Topic

ntn104
Posting Yak Master

175 Posts

Posted - 2011-11-01 : 21:16:05

Hello all,

How do I get automatic assigned id for each row in sql....
For example, I wrote below select statement for keep track how many notes that users added in the table....

select acct_no, note_date, note_text, users
from tnotes

but I want to have another column name "ID" that automatic assigned id for each note_text. How could I do it?

Thanks,

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-11-01 : 21:20:21

Do you want to store the automatic id or just have it in the result set? And are you using SQL Server 2000? I just want to be sure as it'll make a difference in the code.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

ntn104
Posting Yak Master

175 Posts

Posted - 2011-11-01 : 21:31:30

yes, I guess I just want to have it in the result set. when I click on the ID =1 field, then I can see detail of first note....

thanks,

quote:
Originally posted by tkizer

Do you want to store the automatic id or just have it in the result set? And are you using SQL Server 2000? I just want to be sure as it'll make a difference in the code.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-11-01 : 22:33:47

So are you using SQL 2000? It's very easy with SQL 2005 or greater, takes some work in 2000.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

ntn104
Posting Yak Master

175 Posts

Posted - 2011-11-03 : 09:31:18

Sorry I did not specify the version of SQL....you can show me the way to handle with SQL 2005 or greater....

Thanks,

quote:
Originally posted by tkizer

So are you using SQL 2000? It's very easy with SQL 2005 or greater, takes some work in 2000.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-11-03 : 09:56:10

Take a look at example A in BOL for ROW_NUMBER() topic: http://msdn.microsoft.com/en-us/library/ms186734.aspx

That should do the trick. ROW_NUMBER() was added in 2005, hence the reason I needed to know the version. You posted your question in a 2000 forum, so needed to make sure.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-11-03 : 10:09:47

Refer this post
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

Madhivanan

Failing to plan is Planning to fail

ntn104
Posting Yak Master

175 Posts

Posted - 2011-11-03 : 10:14:51

Thanks, that 's good reference...
I just happened to know that we can use NUM(*) for all sql versions...
for example:

select number(*) as row_number, accounts, notes
from tnotes
order by notedate

quote:
Originally posted by tkizer

Take a look at example A in BOL for ROW_NUMBER() topic: http://msdn.microsoft.com/en-us/library/ms186734.aspx

That should do the trick. ROW_NUMBER() was added in 2005, hence the reason I needed to know the version. You posted your question in a 2000 forum, so needed to make sure.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-11-03 : 15:14:32

NUM(*)/number(*)!? What?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources