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?
BitsqueezerConnect With a Mentor Commented:

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

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 ?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.