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 |
|
Dennis Falls
Starting Member
41 Posts |
 Posted - 2003-12-06 : 22:13:59
|
| In my table tbldiagcode I need to sequentially number each record for each Unique ID as followsID code order--- ----- ------111 987.23 1111 675.87 2111 493.09 3222 798.88 1222 394.99 2222 297.00 3From what I've researched so far, it looks like this can be done with a trigger on Insert but am not sure how to code it.I have found the following code in another thread but am not sure what to do with it.select @lineno= select isnull(count(1),0)+1 from inserted iinner join tablename ton i.orderno=t.ordernoAny help would be greatly appreciated.Dennis |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-12-06 : 23:59:15
|
| I'm not a trigger kind of guy myself.You could just calculate the order and stuff it in when a row is inserted.Looks like the "order" is the sort order of "code" within each ID. It looks like the select you mentioned calculates the order of insertion (not the same thing).I'd like to see an UPDATE that would calculate ORDER as you want it for each row in the table.Sam |
 |
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-12-07 : 02:09:33
|
Dennis, what is the business rule behind the "order" column? That is, how is the value in the order determined? Seems to me that as the code value decreases, the order value increases, is that so? Or does the last (inserted) row always have the highest order number?Owais
We make a living out of what we get but we make a life out of what we give. |
|
|
|
Dennis Falls
Starting Member
41 Posts |
Posted - 2003-12-07 : 09:27:45
|
| Actually, it has nothing to do with the code, that is just coincedental in my sample. The order is the order in which they appear in my source file and therefore inserted into my table. We are assuming that the first code is the primary diagnosis code which is important in our research. |
|
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2003-12-07 : 10:00:04
|
| It is difficult to determine what you are trying to accomplish from your sample data and SQL.Can you post a sample of your actual source file and actual desired output?Can you elaborate on what defines uniqueness and ordering among the data?Also are you performing incremental updates/inserts to tbldiagcode or reloading the entire table every time? |
|
|
|
|
|
|
sequential numbering