SQL Server Performance Forum – Threads Archive
Sequential numbering
Hello, I have a table which looks as follows:LINE_NO INT,
ITEM_CD FLOAT,
DESCR VARCHAR(20) and data which looks as follows
0 123 NAME1
0 111 NAME2
0 999 NAME3
…
0 222 NAME9999 i am looking for an SQL command which will give sequential numbers to the LINE_NO columns.
I tried the following, but it taks too long to run:
while ( select count(*) from ##tmpCustOrder where LINE_NO=0)>0
BEGIN
update ##tmpCustOrder
set LINE_NO= 1 +
(select max(LINE_NO) from ##tmpCustOrder)
where ##tmpCustOrder.ITEM_CD = (select top 1 ITEM_CD FROM ##tmpCustOrder WHERE LINE_NO=0)
and LINE_NO=0
END thank you!
For this u can use identity function in sql server ref Books Online, topic is
IDENTITY FUNCTION
Thanks Rushendra
If your table is not too big and you have a unique field in it (better if indexed), than this should work ok
SELECT a.orderid, count(a.orderid)
FROM Northwind.dbo.orders a with (Nolock)
INNER JOIN Northwind.dbo.orders b with (nolock)
ON a.orderid > b.orderid
GROUP BY a.orderid
ORDER BY 2 EDIT: I assumed that you are familier with IDENTITY and it does not serve your needs since it can have identity holes.
If this is not the case, the best choice would be to use IDENTITY column as the other suggested. Bambola.
If you are designing a new table then define the field as IDENTITY with an INT type (if less than 2,147,483,647). There are options that allow you to seed the column with an initial value and also define the increments by which the value should increase. If this has not been done initially, create a new table with data from the orginal table as follows – Select IDENTITY (int,1,1) as LINE_NO,
OriginalTableName.ITEM_CD,OriginalTableName.DESCR
into NewTableName
from OriginalTableName This will replace the create a column with a seed of 1 and increment of 1. You will now need to remove (I suggest rename temporarily) your OriginalTable and rename your new table to the original table name. If this is a regular process that needs to be undertaken you can create a package to undertake the work. Note that this will create an IDENTITY column so all new rows added will be incremented. To change the initial seed value or increment, change the second and third parameters of the IDENTITY function e.g. IDENTITY (int,4,5) will start at 4 and increment by 5
]]>