Avatar of dwechter
dwechter asked on

Composite Key With "Dynamic" Identity Column or Something Similar

Hi All -

Relatively simple idea here, but one that has me a bit stumped as to the best solution.  I want to create an MS SQL table that will store comments related to account issues.  I want to identify, in a composite key, the account number and the incremental comment number (1, 2, 3).  When a new account number is added, again I want to identify that comment number incrementally.  A composite of ACCT_NUM (integer) and COMMENT_NUM (identity integer) works for the first account, but the identity number will continue upward indefinitely and will not properly reset to number the comment by account.

So, experts, any ideas to create the following?  The preference would be that it happens at the table level, and not in an insert command (if possible)!

ACCT_NUM    COMMENT_NUM
987654           1
987654           2
456123           1
456123           2
456123           3
222222           1
333333           1

Thanks!
Microsoft SQL Server 2005SQL

Avatar of undefined
Last Comment
dwechter

8/22/2022 - Mon
SOLUTION
Jim Dettman (EE MVE)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
dwechter

Hi Jim,
I suppose that would work, thanks.  The ideal solution is still a number though, because it's easier to discuss over the phone.  For example, 2 users at different locations looking at an ASP page (fed from this data) with an account with 20 comments.  It would be easier for them to say something like "refer to comment 13, that's where the issue is."

Dan
ASKER CERTIFIED SOLUTION
Mark Wills

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Mark Wills

Sorry, the order by ID should be ASC not DESC...
ASKER
dwechter

Perfect!  I'll use the ACCT_NUM and a DTTM as a composite key, and extract and display using the query above, which works perfectly.  Thanks all.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23