Lauren Quantrell (la*************@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,
so****@algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp