Generating/Updating a Sequence Number in Sql Server T-SQL
Jun
27
Written by:
Wednesday, June 27, 2007 12:52 AM
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.
4 comment(s) 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
|
Re: Generating/Updating a Sequence Number in Sql Server T-SQL
sorty by sortvalue ASC???? what is that?
that does not execute . I have tried order by here and it fails.
how would you do this if the table itself is not already oredered the way you need?
By nick on
Wednesday, January 11, 2012 4:15 AM
|
Re: Generating/Updating a Sequence Number in Sql Server T-SQL
@nick : should be sort by sortvalue
It's pseudocode, not real t-sql. You should use the Order By statement if implementing in t-sql.
By Bruce Chapman on
Wednesday, January 11, 2012 9:38 AM
|