XSolvo
Current user:   guest    Change     Preferences 
   List directory   History   Similar   Print version 
XSolvo 
   About XSolvo 
Articles 
html2xml 
tradufix 
TVProgramGrabber 
download 
Philosophy 
gallery 

Articles > Number sequences

 
rw-rw-r--   Stefan   wheel

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


Reference Number sequences
http://www.xsolvo.com/Articles/NumberSequences

Comments: 0 New comment

Prev. Content   Regular Expressions in SQL Server Next