asked on

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

ASKER CERTIFIED SOLUTION

VipulKadia

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rajkumar Gs

VipulKadia's suggestion works!

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

pivar

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

sachinpatil10d

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

barlet

ASKER

Great work!
Thank you