CODEWITHSUNDEEP
sql-server
Rooban
Rooban

Reputation: 248

Update a column with sequence numbers without using ROW_NUMBER() in SQL Server

I have a table like this and I have difficulty updating it

code    descd   slnum
--------------------- 
10       a        0
10       b        0
12       c        0
12       d        0
11       e        0
12       f        0

I have to update this table like this without using ROW_NUMBER() only by using if else loops how can I do that?

code    descd   slnum
----------------------
10       a        1
10       b        2
12       c        1
12       d        2
11       e        1
12       f        3

Upvotes: 1

Views: 651

Answers (3)

Sh_mispl
Sh_mispl

Reputation: 35

;WITH rownum(code, descd, slnum) AS ( SELECT 10, 'a', 0 UNION SELECT 10, 'b', 0 UNION SELECT 12, 'c', 0 UNION SELECT 12, 'd', 0 UNION SELECT 11, 'e', 0 UNION SELECT 12, 'f', 0 ) SELECT code, descd, COUNT(*) OVER (PARTITION BY code ORDER BY code
) FROM rownum ORDER BY descd

Upvotes: 0

neer
neer

Reputation: 4082

Try this

DECLARE @Tbl TABLE(code VARCHAR(5), descd VARCHAR(5), slnum int)
INSERT INTO @Tbl
( code, descd)
VALUES
('10',       'a'),    
('10',       'b'),    
('12',       'c'),    
('12',       'd'),    
('11',       'e'),    
('12',       'f')     


DECLARE @TempTable TABLE(code VARCHAR(5), descd VARCHAR(5), RowId int)
DECLARE @RowId INT = 1

WHILE @RowId <= (SELECT COUNT(*) FROM @Tbl)
BEGIN

    INSERT INTO @TempTable 
    SELECT TOP 1 
        T.code ,
        T.descd ,
        @RowId       
    FROM 
        @Tbl T LEFT JOIN 
        @TempTable L ON L.code = T.code AND L.descd = T.descd
    WHERE
        L.code IS null 
    ORDER BY 
        T.code,
        T.descd

    UPDATE @Tbl
    SET slnum = (SELECT COUNT(1) FROM @TempTable A WHERE [@Tbl].Code = A.Code)  
    WHERE
        [@Tbl].Code = (SELECT TOP 1 Y.code FROM @TempTable Y WHERE RowId = @RowId) AND
        [@Tbl].descd = (SELECT TOP 1 Y.descd FROM @TempTable Y WHERE RowId = @RowId)


    SET @RowId += 1     
END

SELECT * from @Tbl

Result:

code    descd   slnum
10      a       1
10      b       2
12      c       1
12      d       2
11      e       1
12      f       3

Upvotes: 0

Senthil_Arun
Senthil_Arun

Reputation: 1078

For SQL 2012+

    ;WITH rownum(code, descd, slnum) AS (
    SELECT 10, 'a', 0
    UNION SELECT 10, 'b', 0
    UNION SELECT 12, 'c', 0
    UNION SELECT 12, 'd', 0
    UNION SELECT 11, 'e', 0
    UNION SELECT 12, 'f', 0
    ) SELECT code, descd, COUNT(*) OVER (PARTITION BY code ORDER BY code         
    ROWS UNBOUNDED PRECEDING) FROM rownum o ORDER BY descd

Upvotes: 1

Related Questions