Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
aiken
Aged Yak Warrior
525 Posts |
 Posted - 2002-09-12 : 12:52:25
|
| I think I know the answer here. I just want to make sure before I go off and do the work.I'm developing an application where I need each "user" to have a set of "items". For each user, the item ID's need to be sequential. That is, user 1 needs to have items 1,2,3, etc, and then user 2 needs to have items 1,2,3, etc. The primary key on the items table will be on the item ID and the foreign key referencing the user's table.I think I'm probably stuck doing the item ID management myself -- figuring out that max item ID for any given user during an insert and incrementing it. Am I right, or am I missing something?Thanks-b |
|
|
smccreadie
Aged Yak Warrior
505 Posts |
Posted - 2002-09-12 : 14:53:52
|
| I think you're stuck. I can't think of anyway to do it if the items have to be sequential. Do they really have to be sequential or does only the display need to be sequential? If the latter is true, you have options of storing the "items" with an identity and expressing them as sequential numbers on the output. |
 |
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-09-12 : 18:28:10
|
| Ya, you are stuck unfortunately.... This will be something you will have to set yourself. I had a simular problem where I had 800k records and needed to add a sequential ID to it... exact same thing as what you've said herehttp://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=17373Kinda a pain, I'm actually really curious if theres an easier way to do it... but I don't think so.-----------------------Take my advice, I dare ya |
|
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2002-09-13 : 00:41:54
|
| Unfortunately, the records themselves need to be sequential, or at least the ID does. It'll be used both as a key for lookups and as data returned from lookups; I guess I could use a normal identity column and then also some kind of local identity column, but in that case I don't see what good the real identity column would do.Thanks-b |
|
|
|
r937
Posting Yak Master
112 Posts |
Posted - 2002-09-13 : 05:45:15
|
| aiken, i can accept the requirement no problem (i've seen weirder), but i'm just curious, why does the user's item id have to be sequential?what happens if there's a deletion, are you going to have to renumber the keys to close the gap?rudyhttp://rudy.ca/ |
|
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2002-09-13 : 08:07:21
|
| I would suggest searching the site for a ranking article ... will show you how to do what you want per user ... you basically group by user and the ItemID (which is unique throughout the entire table) can be your base for ranking ... assuming ItemID 501 is #1 for user 123 and ItemID 608 is #2 for user 123 etc...hell here is the article/reader challengehttp://www.sqlteam.com/item.asp?ItemID=4241 -> Challengehttp://www.sqlteam.com/item.asp?ItemID=6398 -> Solution |
|
|
|
|
|
|
|
|
Multi-column identity?