Number sequences

An easy way to create the number sequence is to use the IDENTITY function.


SELECT  IDENTITY(int,1,1) as RowNum,
        COLUMN1,
        COLUMN2
INTO    #worktable
FROM    XYZ

The use of IDENTITY function and DISTINCT create some problem because the IDENTITY function will always create a unique row. One way to solve the problem is using group by or to use a column that's initially is empty and later update it with a unique rowid.

SELECT CONVERT(int,null) as RowNum
INTO    #worktable
FROM    XYZ

DECLARE @ROWS int
SET @ROWS = 0
    
-- when you want to create the sequence for all rows
UPDATE    #worktable SET @ROWS=@ROWS+1,
        RowNum = @ROWS

If you want to have a sequence with different start values for a certain group you can use the same technique but you must set the start value before updating the sequence field.

The sequence will be calculated in the update statement before the data is updated.

--
-- first you create a temp table with the initial value for the sequence
--
SELECT (
    SELECT COALESCE(MAX(COUNTER) ,0)
    FROM ZZZ
    WHERE ZZZ.GROUPCOL = XYZ.GROUPCOL
    ) as RowNum
INTO    #worktable
FROM    XYZ

DECLARE @ROWS int
SET @ROWS = 0

--
-- when RowNum has a start value and you wan't to increase that value
--
UPDATE    #worktable SET @ROWS=@ROWS+1,
        RowNum = RowNum+@Rows



Driven by coWiki 0.3.4-dev web collaboration tool.