It's a common enough problem : you have a table with a sequence number, or sort number, or similar.  It's just a number that orders a set of records for you.

But then you have to delete one of the middle records, or insert one, or something changes. Your sequence number is no longer in sequence.  What to do?

I've seen some truly bizarre implementations of code for this, but there is a simple, effective and fast way of achieving this seemingly simple task.

To explain, imagine a table like this:

CREATE TABLE MySortedTable (MyValue varchar(5), SeqNum int)

To assign an incrementing sequence number to the SeqNum column, all you need to do is this :

/* T-sql code*/ 
declare @seq int
set @seq = 0
UPDATE MySortedTable
SET @seq = SortValue = @seq + 1

 

That's it! Sql Server will update each value in the table, and increment the variable @seq with each row.

2 comments so far...

Re: Generating/Updating a Sequence Number in Sql Server T-SQL But it's not sorted, is it? It assigns an incrementing number to all the records, but not necessarily in the order that you want them ordered in. By Craig on   Tuesday, November 06, 2007 6:28 AM

Re: Generating/Updating a Sequence Number in Sql Server T-SQL @Craig I called it 'mysortedtable' for a reason - the assumption is you have the records in the order that you want them. If not, the sql is just: update mysortedtable set @seq= sortValue = @seq + 1 sorty by sortvalue asc -Bruce By Bruce on   Tuesday, November 06, 2007 7:11 AM

Your name:
Title:
Comment:
Security Code Enter the code shown above in the box below
Add Comment    Cancel