[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 241
  • Last Modified:

Increment certain field after given limit

Hello Experts

For example I have 1 table with 2 columns 'names' and 'groups'

I am doing simple insert into

insert into test(names, groups) values('aa' , 1)
insert into test(names, groups) values('bb' , 1)
insert into test(names, groups) values('cc' , 1)
insert into test(names, groups) values('dd' , 1)
insert into test(names, groups) values('ee' , 1)
insert into test(names, groups) values('ff' , 1)
insert into test(names, groups) values('gg' , 1)

and i want each group to contain for example 3 names

so after third insert the fourth should add groups =2
and after three inserts groups =3 and so on

Output should be something like this:
Names   Groups
aa              1
bb              1
cc              1
dd              2
ee              2
ff               2
gg              3
0
barlet
Asked:
barlet
1 Solution
 
VipulKadiaCommented:
Before every INSERT, do following :

DECLARE @RowCount BIGINT
SELECT @RowCount = COUNT(*) FROM  test
SET @RowCount = (@RowCount / 3) + 1
insert into test(names, groups) values('aa' , @RowCount)

0
 
Rajkumar GsSoftware EngineerCommented:
VipulKadia's suggestion works!

VipulKadia: One similarity between us - We both registered in Experts-Exchange in the same date - 10/15/09 :)
0
 
pivarCommented:
Hi,

If you want to do it in one update, you could try:

update test
set groups=(cnt-1)/3+1
from (select names, ROW_NUMBER() OVER (order by names asc) as cnt from test) a
where a.names=test.names

/peter
0
 
sachinpatil10dCommented:
Try this

select *, ROW_NUMBER ( ) OVER (PARTITION  BY (id%3) order by id) grp from (
select *, ROW_NUMBER ( ) OVER (order by names) id from test)t
order by id

Open in new window

0
 
barletAuthor Commented:
Great work!
Thank you
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now