Results 1 to 7 of 7

Thread: Sequence number

  1. #1
    Join Date
    Oct 2003
    Posts
    2

    Question Unanswered: Sequence number

    I have a situation where I need to generate a sequence number based on the value of another column.
    For Ex. There is an accounts table with accounts id as primary key. I want to have another column (let us say) Tx_no, which should be a sequnce number starting from 1 for each account id. The table should like this.

    Accounts id Transaction No
    1100 1
    1100 2
    1100 3
    1100 4
    1200 1 -- Note that seq.# starts from 1 again.
    1200 2

    Is there any way I can generate the sequence # in the second column automatically

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Declare @AccountsID Int
    set @AccountsID = 1200

    Declare @NextIDNum int
    Set @NextIDNum = Isnull((Select Max([Transaction No]) from Accounts where [AccountsID] = @AccountsID), 0) + 1

    Not sure if you can put this logic in a default, but you could put it in an insert trigger.

    blindman

  3. #3
    Join Date
    Sep 2003
    Location
    New York, NY
    Posts
    136
    make a stored procedure that will

    1). read the accounts table and move accounts_ID to a cursor sort by accounts_ID inc.

    2). read the cursor one by one and check

    if prev_account_ID = present_account_ID
    count = count + 1
    else
    count = 1
    prev_account_ID = present_account_ID
    end if

    3). move this count to an update step which will update the tx_ID field using "current of accountID" and value in stored in the variable "count".

  4. #4
    Join Date
    Oct 2003
    Posts
    2
    Originally posted by rohitkumar
    make a stored procedure that will

    1). read the accounts table and move accounts_ID to a cursor sort by accounts_ID inc.

    2). read the cursor one by one and check

    if prev_account_ID = present_account_ID
    count = count + 1
    else
    count = 1
    prev_account_ID = present_account_ID
    end if

    3). move this count to an update step which will update the tx_ID field using "current of accountID" and value in stored in the variable "count".
    This is what I was thinking to do. But I was wondering if there are any other ways.
    Thanks for the reply,

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Take the logic that I gave you and put it in an INSERT trigger on your table and you should be all set.

    blindman

  6. #6
    Join Date
    Oct 2003
    Posts
    706

    Exclamation

    Originally posted by subash
    This is what I was thinking to do. But I was wondering if there are any other ways.
    Thanks for the reply,
    No, I think that would be the best. Remember that the sequence-number should be in a separate column from the main ID-number!

    (If you need a value which combines the two, either use a calculated field or store the combined result in a third column...)

    Also remember that neither of these should be used as the "real primary key." It can be a 'unique' column and all of that, but it is not wise to use such values as the primary-keys as seen by the computer. (Not only is it an "encoded value," containing information, but it's also probably a "human-known value," and humans are not as rigorous as DBMSes require..
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "No, I think that would be the best."
    - I'm sorry. You think what would be best? Loading all the Account_IDs into a cursor and stepping through it one at a time incrementing a counter? For EVERY INSERT?

    "Remember that the sequence-number should be in a separate column from the main ID-number!"
    - I had no idea this was in question.

    "Also remember that neither of these should be used as the 'real primary key.' It can be a 'unique' column and all of that, but it is not wise to use such values as the primary-keys as seen by the computer."
    - You are confusing guidlines with rules.

    sundial, please read previous posts before responding to threads, and make sure your comments are in an area of expertise.

    blindman

Posting Permissions

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