August 1, 2011 at 8:46 am
Can anyone tell me why the below code in example 1 works, but the code in example 2 doesn't work?
The code is supposed to add a sequence number to each row, starting at 1 and incrementing by 1, for each distinct value, resetting to 1 for each new group.
I am running it on SQL Server 2000.
The difference with example 2 is that it has a GO statement in the middle, just after the insert.
Why would splitting example 2 into two batches cause it to no longer to work in the same way as example 1?
-------------------------------------------------------------------
--Example 1:
IF EXISTS (SELECT Name FROM SysObjects WHERE Name = 'seq_nos_test_dm') DROP TABLE seq_nos_test_dm
CREATE TABLE seq_nos_test_dm (
Value int,
Seq int)
INSERT INTO seq_nos_test_dm (Value)
SELECT 101 UNION ALL
SELECT 104 UNION ALL
SELECT 101 UNION ALL
SELECT 102 UNION ALL
SELECT 104 UNION ALL
SELECT 103 UNION ALL
SELECT 102 UNION ALL
SELECT 102 UNION ALL
SELECT 104 UNION ALL
SELECT 102;
DECLARE
@iSeq int,
@iLastVal int
SET @iSeq = 0
SET @iLastVal = 0
UPDATE T
SET
@iSeq =
CASE
WHEN @iLastVal = T.Value THEN @iSeq + 1
ELSE 1
END,
@iLastVal = T.Value,
T.Seq = @iSeq
FROM seq_nos_test_dm T
INNER JOIN (SELECT TOP 100 PERCENT Value
FROM seq_nos_test_dm
ORDER BY Value
) T2
ON T.Value = T2.Value
SELECT * FROM seq_nos_test_dm ORDER BY Value, Seq
--------------------------------------------------------------------------
--Example 2:
IF EXISTS (SELECT Name FROM SysObjects WHERE Name = 'seq_nos_test_dm') DROP TABLE seq_nos_test_dm
CREATE TABLE seq_nos_test_dm (
Value int,
Seq int)
INSERT INTO seq_nos_test_dm (Value)
SELECT 101 UNION ALL
SELECT 104 UNION ALL
SELECT 101 UNION ALL
SELECT 102 UNION ALL
SELECT 104 UNION ALL
SELECT 103 UNION ALL
SELECT 102 UNION ALL
SELECT 102 UNION ALL
SELECT 104 UNION ALL
SELECT 102;
go
DECLARE
@iSeq int,
@iLastVal int
SET @iSeq = 0
SET @iLastVal = 0
UPDATE T
SET
@iSeq =
CASE
WHEN @iLastVal = T.Value THEN @iSeq + 1
ELSE 1
END,
@iLastVal = T.Value,
T.Seq = @iSeq
FROM seq_nos_test_dm T
INNER JOIN (SELECT TOP 100 PERCENT Value
FROM seq_nos_test_dm
ORDER BY Value
) T2
ON T.Value = T2.Value
SELECT * FROM seq_nos_test_dm ORDER BY Value, Seq
---------------------------------------------------------------------------
August 1, 2011 at 8:55 am
Are you sure Example 1 works? This is what I get:
Value Seq
1011
1011
1021
1021
1021
1022
1031
1041
1041
1041
John
August 1, 2011 at 9:08 am
Example 1 gives me:
Value Seq
1011
1012
1021
1022
1023
1024
1031
1041
1042
1043
Example 2 gives me:
Value Seq
1011
1011
1021
1021
1021
1022
1031
1041
1041
1041
August 2, 2011 at 3:40 am
I get the same results for both examples. I can't see how putting in a GO will make any difference.
John
August 2, 2011 at 6:08 am
That's strange. I wonder why you are getting different results to me. My example 1 and 2 results are using sql server 2000, and they give the same results every run.
Are you running example 1 through in a single batch?
Rgds,
August 2, 2011 at 6:12 am
Yes, I am, and to be honest, I'd be surprised if I saw what you're seeing. I wish I knew how you do it!
John
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply