How to generate an additional Identity column in a query

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

SQL Server Development (2000)

How to generate an additional Identity column in a query

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-05-09 : 08:23:48

Tao writes "Say we have a table like this:

ProjectID, UserID, comments, AddedTime

Here are some sample data:

212, 44, 'Start now', '5/1/2005'

345, 44, 'Start now', '5/1/2005'

212, 46, 'Finish', '5/2/2005'

Here are the result I want for Project 212:

1, 212, 44, 'Start now', '5/1/2005'
2, 212, 46, 'Finish', '5/2/2005'

Did you that I want 1, 2 as an sequence number based on addeddate ?

It seems that I may need to join the table to itself."

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-05-09 : 09:02:16

Just dump into a temp table with an ID field.

Select IDENTITY (int, 1, 1) as IDfield,ProjectID, UserID, comments, AddedTime
Into #tempProject
From yourtable
Order by AddedTime

Select IDfield,ProjectID, UserID, comments, AddedTime
from #tempProject
order by IDfield

Jim
Users <> Logic

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-09 : 10:35:08

"You're dangerous"

"That's right ice....man...I am dangerous"

Don't you alread have a sequesnce in the original IDENTITY column?

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources