Increment certain field after given limit

Posted on 2011-10-20
Last Modified: 2012-05-12
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
Question by:barlet
    LVL 8

    Accepted Solution

    Before every INSERT, do following :

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

    LVL 23

    Expert Comment

    by:Rajkumar Gs
    VipulKadia's suggestion works!

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

    Expert Comment


    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

    LVL 9

    Expert Comment

    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

    LVL 7

    Author Closing Comment

    Great work!
    Thank you

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    This video discusses moving either the default database or any database to a new volume.
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now