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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-05-03 : 08:32:17
|
| Ron writes "I have a database where the primary key is created from a combination of five fields. I want to be able to create a number of each occurance of a unique identifier so I can number the records 1 through however many there are. For example:Field1 Field2 Field3 Field423142 $800 12/1/01 Yes23142 $800 10/4/01 Yes23142 $800 8/14/01 Yes12333 $700 1/1/01 NoI'd like to be able to add a number to the first field so that it would show the first occurance of 23142, the second, and so onHope this makes sense." |
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2005-05-03 : 11:09:27
|
select <Column List> from YourTable where YourPrimaryKey = YourKeyValueNow you have all occurances of your duplicates. If you just want the count thenSelect count(*) from YourTable where YourPrimaryKey = YourKeyValueOtherwise, you need to create a new column, and fill that column in when you add a record to the table. Sounds like a job for Trigger man. Oh, you want the CODE to do it.....Cant help you there.Take your dupes, fill in the numbers in your new column.Do this for all values in your table.Now put in your trigger to check on insert if a "dupe" key exists. If so, insert max +1 into your dupe count column. Otherwise, put in 1Hopefully, one of the brainiacs here will give you some code to start you off *coughRobVolkcough* |
 |
|
|
|
|
|
counting occurances of duplicate records