Create sequential numbers in a column

I have a temp table that's populated with an insert query in as tored
procedure. The temp table has a uniqueID as the primary key.
In that table I have a column SortOrder.
What I want to do is to create a sequential number in SortOrder but
only for records matching a WHERE statement, for example:
(pardon the shorthand...)
Insert *.tblPermanent into tblTemp
If myField = 1 then
SortOrder = 1(2,3,4,5,....etc.)
else
SortOrder = 0
Thanks
lqLauren Quantrell (laurenquantrell@.hotmail.com) writes:
> I have a temp table that's populated with an insert query in as tored
> procedure. The temp table has a uniqueID as the primary key.
> In that table I have a column SortOrder.
> What I want to do is to create a sequential number in SortOrder but
> only for records matching a WHERE statement, for example:
> (pardon the shorthand...)
> Insert *.tblPermanent into tblTemp
> If myField = 1 then
> SortOrder = 1(2,3,4,5,....etc.)
> else
> SortOrder = 0

There are a lot of things that I don't know about, so I have to make
a guess. First, I make the guess that the tblPermanent has a primary-
key column called id. In such case, you can do:

INSERT tblTemp(id, sortorder, ....)
SELECT id, (SELECT COUNT(*)
FROM tblPermanent b
WHERE b.id >= a.id
AND b.myfield = 1
AND a.myfield = 1), ...
FROM tblPermanent

If this does answer your question, please provide the following:

o CREATE TABLE statements for your table.
o INSERT statements with sample data.
o The desired result from the sample data.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp