How can I add a sequencial column

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 2005 Forums

Transact-SQL (2005)

How can I add a sequencial column

Author

Topic

johnstern
Yak Posting Veteran

67 Posts

Posted - 2007-09-20 : 12:12:22

I have a select statement that questies a table

id name
3 john
9 lucas
12 jinice

I want to get something like this

id name seq order
3 john 1
9 lucas 2
12 jinice 3

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-20 : 13:13:45

look into identity columns and row_number() function.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

johnstern
Yak Posting Veteran

67 Posts

Posted - 2007-09-20 : 14:09:39

thank you that did it :)

select UserID, TestID, UserDate, UserTypeID, ROW_NUMBER() OVER(ORDER BY UserID ASC) AS 'Row Number'
from Random_User where testID = 58

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-21 : 03:20:09

quote:
Originally posted by johnstern

thank you that did it :)

select UserID, TestID, UserDate, UserTypeID, ROW_NUMBER() OVER(ORDER BY UserID ASC) AS 'Row Number'
from Random_User where testID = 58

1 Avoid having space in object names
2 Use underscore in place of space
3 Instead of single quote use []

Madhivanan

Failing to plan is Planning to fail

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2007-09-21 : 05:27:29

SELECT id,name,
,ROW_NUMBER() OVER(ORDER BY id ASC) AS 'seq order'
FROM myTable

Jack Vamvas
--------------------
Need an IT job ? http://www.ITjobfeed.com

Kristen
Test

22859 Posts

Posted - 2007-09-21 : 05:40:17

That looks to be what the OP did, including the single quotes which you should avoid using as Madhi said!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-21 : 06:38:29

quote:
Originally posted by jackv

SELECT id,name,
,ROW_NUMBER() OVER(ORDER BY id ASC) AS 'seq order'
FROM myTable

Jack Vamvas
--------------------
Need an IT job ? http://www.ITjobfeed.com

So you dont read what others posted?

Madhivanan

Failing to plan is Planning to fail

johnstern
Yak Posting Veteran

67 Posts

Posted - 2007-09-21 : 10:01:04

thank you for the tips

js

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources