CODEWITHSUNDEEP
sqlsql-servert-sql
Hemingway Lee
Hemingway Lee

Reputation: 769

Update column according to row number using T-SQL

I have a table like this

ID          Group
----------- ----------- 
2           NULL
4           NULL
7           NULL
8           NULL
11          NULL
12          NULL

I want to update my table according to row number like this

ID          Group
----------- ----------- 
2           G_1
4           G_1
7           G_2
8           G_2
11          G_3
12          G_3

First N rows ---> G_1,

N+1 to 2N rows ---> G_2,

2N+1 to 3N rows ---> G_3.

...

How can i achieve this?

Thanks in advance.

Upvotes: 1

Views: 166

Answers (4)

D Stanley
D Stanley

Reputation: 152511

Assuming you're using SQL 2005 or later:

DECLARE @n integer
SELECT @N = 3;

WITH rows AS
(
  SELECT
     ID, 
     ROW_NUMBER() OVER (ORDER BY ID) RowNum   
  FROM temp
)
UPDATE temp
SET [Group] = 'G_' + CONVERT(VARCHAR(4),((RowNum-1) / @N) + 1)
FROM temp
INNER JOIN rows ON temp.ID = rows.ID

Upvotes: 2

t-clausen.dk
t-clausen.dk

Reputation: 44316

declare @N int = 2

declare @t table(ID int, Grp char(3))

insert @t(id) values(2),(4),(7),(8),(11),(12)

;with x as
(
  select id, Grp,
  (row_number() OVER(ORDER BY ID)+1) / @N rn
  from @t
)
update x 
set grp = 'G_' + cast(rn as varchar(9))

select * from @t

Result:

ID  Grp
2   G_1
4   G_1
7   G_2
8   G_2
11  G_3
12  G_3

Upvotes: 2

clhereistian
clhereistian

Reputation: 1300

DECLARE @n INT
SET @n = 5

UPDATE my_table
SET [Group] = 'G_' + CAST((row_nums.row_num / (@n + 1)) + 1 AS VARCHAR) 
FROM
(SELECT 
    my_table.ID,
    my_table.[Group],
    ROW_NUMBER() OVER (ORDER BY my_table.ID) row_num
From my_table 
) row_nums
WHERE row_nums.ID = my_table.ID

Upvotes: 2

RubberDuck
RubberDuck

Reputation: 12728

Why update at all? It's kind of poor form to store calculated values. What happens if your ID would have to change in the future? (It really shouldn't if it's the primary key, but I live in the real world where real life happens.)

SELECT id,
    CASE 
        WHEN id <= n and id > 2*n then 'G_' + '1'
        WHEN id <= 2*n and id > 3*n then 'G_' + '2'
        WHEN id < 3*n then 'G_' + '3'
    END AS GROUP

Depending on your purpose, you could put this into a view, or a calculated column.

Upvotes: 0

Related Questions