adding an ID field - SQL Server Forums

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)

adding an ID field

Author

Topic

Josie
Starting Member

1 Post

Posted - 2009-01-09 : 05:51:42

Morning

I want to add an ID field to an existing table, but I want to add it with the records in a particular order, cos I want to use the ID in queries grouping the records

what do I need to add to:

alter table performance
add ID int identity(1,1)

to make sure the ID is added like so:

order by specialty asc, activity desc

thanks

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-09 : 05:54:15

Hi,

Just add an identity column as u specified above and While retreiving get the records as
order by specialty asc, activity desc.

revelator
Starting Member

32 Posts

Posted - 2009-01-09 : 06:23:42

What is your intended purpose of the ID column? The order should make no difference.

Waiting for the Great Leap Forwards

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-09 : 06:28:56

try like this
select identity(int,1,1)as id ,* into #temp from performance order by specialty asc,activity desc
select * from #temp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-09 : 06:31:14

seems like what you're asking is


alter table performance 
add ID int null

UPDATE t
SET ID=Seq
FROM
(
SELECT ID,ROW_NUMBER() OVER (order by specialty asc, activity desc) AS Seq
FROM performance
)t

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources