adding identity column in sql

HI , i want to add a indenity column
to my table sorted by raw_score in the descending order..
I tried the below code but it throws an error. can any one
please give me the proper tsql code for this ??

my code --

alter table mytale add id indenity (1,1)
order by raw_score desc
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

you can't have the identity column start in a particular order, you just add it to the table and it will be based on the clustered key of the table.

alter table mytale add id indenity (1,1)
Or you could create an empty table with an identity column already in it, select the records from the original and put them in the copy table in a sorted order.

maybe you should try to correct your spelling first - with the word "indenity" you will not get anything in SQL Server.

The primary key normally is used as an identifier for a row and therefore is used with an identity setting so SQL Server generates the numbers on its own. There's no way to get it in a descent order, it always counts up. And that makes sense because the primary key is in most cases used as a clustered index which means that this index is the physical order of the table. The next row which should be saved of course is saved at the end of the table, a descent order would mean to move all the rest of the table one record down to insert the next at the top - nobody would do this.

But you can use the identity column without descent order in the way I wrote above, because ordering is not the job of a table but the job of the SQL command you use to query the data. So you can simply use a SQL SELECT and add an "ORDER BY raw_score DESC" at the end to get the table you want.


gvamsimbaAuthor Commented:
Thanks guyz....actually in my table , i have a column named raw_score

so basically, i need top 60000 from mytable based on the raw_score in descening order....of which the top 30000
needs to be flagged as 1 and the next 30000 should be flagged as 2 , but all the records to be flagged should be based on the raw_score in descening order....

what is the best code to achieve this ?

you could do something like in the attached code. It generates two queries, one with the top 30000 rows from "OriginalTable" and the second with the next 30000 (you need to adjust the number to the correct value instead of 30000). The first query always uses "1" as first column, the second always "2".
You must add all the columns you want to transfer in "columnlist" / "samecolumnlist", in both cases in the same order.



INSERT INTO myTable (flagcolumn,columnlist,....)
SELECT 1 AS flagcolumn,samecolumnlist.... FROM (SELECT TOP (30000) samecolumnlist....
		              FROM OriginalTable
                              ORDER BY raw_score DESC) AS Part1
SELECT 2 AS flagcolumn,samecolumnlist.... FROM (SELECT TOP (30000) samecolumnlist....
  		                FROM OriginalTable
                                   WHERE raw_score < 30000 -- change the number to the lower raw_score list value which should be flagged as 2
                                   ORDER BY raw_score DESC) AS Part2

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.