Post reply

Incrementing inside a group

  • April 22, 2009 at 8:53 am

    #133403

    I know I've seen this before, but I haven't had success Googling it.

    I have a table like this

    create table #tmp

    (

    iuserid int,

    vchGroupName varchar(50),

    vchIncrement varchar(20)

    )

    The data I have would look like this

    insert #tmp

    select 1,'groupa',''

    union all

    select 2,'groupa',''

    union all

    select 3,'groupa',''

    union all

    select 4,'groupb',''

    union all

    select 5,'groupb',''

    and the result I'm looking for is

    1,groupa,1

    2,groupa,2

    3,groupa,3

    4,groupb,1

    5,groupb,2

    there are articles on doing this for 2005 with the over clause but I couldn't find one for 2000

    Thanks 🙂

  • April 22, 2009 at 9:02 am

    #982252

    Jeff Moden's excellent article here[/url] describing how to do this is currently being rewritten to accommodate changes between SQL2k and SQL2k5. However, the SQL2k code, which is what you're interested in, can be found in the link under Resources.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

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

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