How to update generated number field with number to every group from 1 to 4 ?

Question

How to update generated number field with number to every group from 1 to 4 ?

ahmed_elbarbary.2010

Hall of Fame

Points: 3118
More actions
June 7, 2021 at 2:21 am

#3892727
I work on SQL server 2012 I face issue I need to update Generated Number on table test
to get UNIQUE number to every group id from 1 to 4
so first group from groupid 1 to 4 will be as 1000
so second group from groupid 1 to 4 will be as 1001
so third group from groupid 1 to 4 will be as 1002
```
create table test
(
FamilyId int,
PortionKey nvarchar(20),
GroupId int,
GeneratedNumber int
)
insert into test(FamilyId,PortionKey,GroupId,GeneratedNumber)
values
(12100,'ab',1,NULL),
(12100,'cd',2,NULL),
(12100,'eg',3,NULL),
(12100,'fb',4,NULL),
(12100,'am',1,NULL),
(12100,'fu',2,NULL),
(12100,'ab',3,NULL),
(12100,'cy',4,NULL),
(12100,'lf',1,NULL),
(12100,'ad',2,NULL),
(12100,'gb',3,NULL),
(12100,'mu',4,NULL)
```
expected result
```
FamilyId PortionKey GroupId GeneratedNumber
12100 ab 1 1000
12100 cd 2 1000
12100 eg 3 1000
12100 fb 4 1000
12100 am 1 1001
12100 fu 2 1001
12100 ab 3 1001
12100 cy 4 1001
12100 lf 1 1002
12100 ad 2 1002
12100 gb 3 1002
12100 mu 4 1002
```
if i get unique number for every group of portion key from 1 to 4 then it accepted for me
never mind 1000 or not but must be unique
portion key on table test not repeated
I need every group id from 1 to 4 to get unique number
based on group id and portion key
thanks

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn SSC-Addicted Points: 424 More actions · Answer 1

You you can use the row_number() window function to generate the number. Here is an example:

create table test
(
FamilyId int,
PortionKey nvarchar(20),
GroupId int,
GeneratedNumber int
)
insert into test(FamilyId,PortionKey,GroupId,GeneratedNumber)
values
(12100,'ab',1,NULL),
(12100,'cd',2,NULL),
(12100,'eg',3,NULL),
(12100,'fb',4,NULL),
(12100,'am',1,NULL),
(12100,'fu',2,NULL),
(12100,'ab',3,NULL),
(12100,'cy',4,NULL),
(12100,'lf',1,NULL),
(12100,'ad',2,NULL),
(12100,'gb',3,NULL),
(12100,'mu',4,NULL)
go

with MyCTE as (
select *,
row_number() over (partition by GroupID order by PortionKey) as rowNum
from test)
select MyCTE.FamilyID, PortionKey, GroupId, RowNum + 1000 - 1
from MyCTE
order by RowNum, GroupId
go

drop table test

Adi

Alan Burstein SSC Guru Points: 61136 More actions · Answer 2

Adi did it like I would. His excellent solution could be simplified as

SELECT t.FamilyID, t.PortionKey, t.GroupID, GeneratorNumber = 
         ROW_NUMBER() OVER (PARTITION BY t.GroupID ORDER BY (SELECT NULL))-1+1000
FROM     #test AS t
ORDER BY ROW_NUMBER() OVER (PARTITION BY t.GroupID ORDER BY (SELECT NULL));

This gets the same execution plan and returns the same result.

It's worth noting that the ORDER BY in both cases is for presentation and adds an additional sort to the execution plan. It's not needed unless you need the rows sorted in that order. In that case I would let whatever application this is driving do the work.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001