Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Location
    Greenwood, IN
    Posts
    40

    Unanswered: increment by group

    We have a system that creates a transaction id. The transaction id is numeric and about 20 digits. I need that transaction id to sequence on but would like a more manageable number... as in an integer.

    How can I create an integer that increments BY the grouping? Example: I have an account number, then transaction id:

    123, 1000000000000000000001
    123, 1000000000000000000589
    123, 4000000000000000003459
    123, 7000000000000000000032
    338, 1000000000000000000004
    338, 1000000000000000000046

    How can I write a sql statement that will change the above into:

    123, 1
    123, 2
    123, 3
    123, 4
    338, 1
    338, 2
    Doug

  2. #2
    Join Date
    Jan 2004
    Posts
    51

    Re: increment by group

    Originally posted by coders4hire
    We have a system that creates a transaction id. The transaction id is numeric and about 20 digits. I need that transaction id to sequence on but would like a more manageable number... as in an integer.

    How can I create an integer that increments BY the grouping? Example: I have an account number, then transaction id:

    123, 1000000000000000000001
    123, 1000000000000000000589
    123, 4000000000000000003459
    123, 7000000000000000000032
    338, 1000000000000000000004
    338, 1000000000000000000046

    How can I write a sql statement that will change the above into:

    123, 1
    123, 2
    123, 3
    123, 4
    338, 1
    338, 2
    I think this can be done using cursors.

  3. #3
    Join Date
    Nov 2003
    Location
    edison, NJ
    Posts
    4
    Try this.

    select id,trid,col3=
    Case when id = id then
    (select count(*) from my a where a.id = my.id and a.trid < my.trid)+1 end
    from my

    Amar

  4. #4
    Join Date
    Dec 2003
    Location
    Greenwood, IN
    Posts
    40
    Perfect, thanks!
    Doug

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •